Tags: abw_event, analyzer, database, drivers, event_id, for20select, max, mdacgt-original, microsoft, mysql, odbc, oracle, performance, query, query-plan, select, server, sql, updated

SELECT MAX(...) performance SQL Server/ODBC

On Database » Microsoft SQL Server

3,629 words with 1 Comments; publish: Fri, 30 May 2008 18:24:00 GMT; (25078.00, « »)

What query-plan does the query analyzer show for=20

SELECT MAX(event_id) FROM abw_event ?

have you updated the odbc drivers with mdac?

>--Original Message--

>I work on a commercial software product that is a=20

database application. ODBC=20

>database connectivity and MFC record set classes are used=20

to access the=20

>database. The software supports three types of databases -

Oracle, Sybase=20

>ASA, and SQL Server. One of our clients has a relatively=20

big SQL Server 2000=20

>SP3a database and they are experience bad performances=20

when data is saved to=20

>the database. The bottleneck for the bad performance=20

appears to be the SELECT=20

>MAX(...) statement that is executed against the table=20

named abw_event.=20

>SELECT MAX(event_id) FROM abw_event;

>Here is DDL for the table:

>CREATE TABLE abw_event (

> event_id int NOT NULL,

> prev_event_id int NULL,

> event_type int NOT NULL,

> on_behalf_of_user int NULL,

> entered_by int NULL,

> event_timestamp datetime NOT NULL,

> event_descr varchar(2000) NULL,

> event_message varchar(2000) NULL,

> how_long_to_keep smallint NULL

>)

>go

>ALTER TABLE abw_event

> ADD CONSTRAINT XPKabw_event PRIMARY KEY=20

NONCLUSTERED (event_id)

>go

>The abw_event table has 53000 rows in it and its primary=20

key is a single=20

>column with int data type. The C++ code issues the above=20

SELECT MAX(...)=20

>command through ODBC function call "SQLExecDirect" that=20

takes 15+ seconds to=20

>execute.=20

>What's interesting is that if I run the same SELECT MAX

(...) command in the=20

>Query Analyzer I get the results instantly. I suspect=20

that this bad=20

>performance has something to do with ODBC driver.=20

>The same SELECT MAX(=E2?=A6) statement executes instantly=20

while using Oracle or=20

>Sybase ASA databases.

>I appreciate your feedback.

>Thanks,

>Mehrdad

>.

>

All Comments

Leave a comment...

  • 1 Comments
    • The query execution plan is something like this:

      SELECT <-- Stream Aggregation <-- Top <--

      abw_event.XPKabw_event

      Cost: 0% Cost: 0% Cost: 0%

      Cost: 100%

      MAX(abw_event.event_id)

      abw_event.XPKabw_event

      ORDERED BACKWARD

      I have the latest SQL Server driver (version 2000.85.1117.00) dated

      8/4/2004. I think the MDAC get updated with Windows XP Update automatically.

      Thanks,

      Mehrdad

      "gandalf" wrote:

      > What query-plan does the query analyzer show for

      > SELECT MAX(event_id) FROM abw_event ?

      > have you updated the odbc drivers with mdac?

      >

      > database application. ODBC

      > to access the

      > Oracle, Sybase

      > big SQL Server 2000

      > when data is saved to

      > appears to be the SELECT

      > named abw_event.

      > NONCLUSTERED (event_id)

      > key is a single

      > SELECT MAX(...)

      > takes 15+ seconds to

      > (...) command in the

      > that this bad

      > while using Oracle or

      >

      #1; Fri, 30 May 2008 18:25:00 GMT