Re: SELECT MAX(...) performance in SQL Server/ODBC

From: Mehrdad (Mehrdad_at_discussions.microsoft.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 07:43:02 -0700

Thanks Brannon for the suggestions.

I tried the Profiler and compared the results and execution plans in the two
cases:
(1) executing the query from SQL Analyzer
(2) executing the query from C++ code by calling SQLExecDirect.

I found no difference between the two profiles. What I noticed was that it
takes 15 seconds to see any activity in the profiler. So I kind of think that
it gets stuck in either MFC or ODBC Driver. I noticed that in our code we are
using the function SQLAllocStmt which is a deprecated ODBC function. I am
going to replace that function call with its (supported) equivalent
SQLAllocHandle to see if that makes a difference.

"Brannon Jones" wrote:

> Well Query Analyzer uses the ODBC driver to send the command to the server,
> so it's not necessarily a problem with the driver, but maybe how it's being
> used (I'm pretty sure that Query Analyzer doesn't use the MFC Database
> classes).
>
> Have you tried using SQL Profiler to profile the events on the server?
>
> Brannon
>
> "Mehrdad" <Mehrdad@discussions.microsoft.com> wrote in message
> news:987B03D5-5C56-47D1-8725-A590EFEBA0FA@microsoft.com...
> > I work on a commercial software product that is a database application.
> ODBC
> > database connectivity and MFC record set classes are used to access the
> > database. The software supports three types of databases - Oracle, Sybase
> > ASA, and SQL Server. One of our clients has a relatively big SQL Server
> 2000
> > SP3a database and they are experience bad performances when data is saved
> to
> > the database. The bottleneck for the bad performance appears to be the
> SELECT
> > MAX(...) statement that is executed against the table named abw_event.
> >
> > 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 NONCLUSTERED (event_id)
> > go
> >
> > The abw_event table has 53000 rows in it and its primary key is a single
> > column with int data type. The C++ code issues the above SELECT MAX(...)
> > command through ODBC function call "SQLExecDirect" that takes 15+ seconds
> to
> > execute.
> >
> > What's interesting is that if I run the same SELECT MAX(...) command in
> the
> > Query Analyzer I get the results instantly. I suspect that this bad
> > performance has something to do with ODBC driver.
> >
> > The same SELECT MAX(.) statement executes instantly while using Oracle or
> > Sybase ASA databases.
> >
> > I appreciate your feedback.
> >
> > Thanks,
> > Mehrdad
> >
>
>
>

Relevant Pages

  • Re: ADO.NET query execution much slower than SQL Management Studio
    ... When you first start using a new database it creates stats. ... the first few queries run slowly, as will any unique type of query, as it ... Something that would affect the .NET SqlClient but not SQL Mgmt Studio? ... compare this to the profiler command (meaning what is actually run in the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO.NET query execution much slower than SQL Management Studio
    ... Run the query twice from ADO.NET and see ... Stats being compiled on SQL Server ... If you are dynamically adding statements in a sproc, ... sproc versus running the sproc statement from profiler), ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Prepared TADOQuery Question
    ... I have the prepared property set to true, but when using the MS SQL ... The profiler results don't change when I open/close the query. ...
    (borland.public.delphi.database.ado)
  • Re: DataAdapter retrieves no row from Oracle left join count query
    ... What results do you get when you try executing the queries below through ... If you run this query in sql plus or similiar tool, ... > suspect this is a bug of the .NET client provider for oracle. ...
    (microsoft.public.dotnet.framework.adonet)
  • query in SQL 2005 profiler in wrapped format
    ... In SQL 2005 profiler, the bottom half of each trace window displays the ... actual SQL query in "wrapped" format (e.g., a single line long query is ... where the actual query is displayed in non-wrapped format. ...
    (microsoft.public.sqlserver.tools)
Loading