|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
A query runs 1 times slower from a .NET application the from Query Analizerdatabase : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest table in the database contains 11,000,000 records. Framework: .NET 2.0 I try to run a query against the database, selecting aggregated data from views based on the large table. When executed from the Query Analizer, it takes 13 seconds. When executed from a .NET application, it takes 140 seconds. The database is well tuned (or else the query analizer would go slowly), so I can't find the reason for this difference. Any suggestion ? TIA Boaz Ben-Porat Milestone Systems Just a guess.
It might be the delay in creating and opening the connection. Why don't you log the current time just before calling the SP and after it and find the time difference. That can narrow down on what the issue is. Show quote "Boaz Ben-Porat" wrote: > Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server > > database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest > table in the database contains 11,000,000 records. > > Framework: .NET 2.0 > > I try to run a query against the database, selecting aggregated data from > views based on the large table. > When executed from the Query Analizer, it takes 13 seconds. > When executed from a .NET application, it takes 140 seconds. > > The database is well tuned (or else the query analizer would go slowly), so > I can't find the reason for this difference. > > Any suggestion ? > > TIA > Boaz Ben-Porat > > Milestone Systems > > > > Thanks for a quick answer.
The time I refer to is after the connection is opened. the relevant code: DbDataReader dr = null; try { // This method opens a connection, if not allready opened Connect(); // dbCommand is an input parameter of type DbCommand. It contains the SQL statement dbCommand.Connection = _connection; DateTime t1 = DateTime.Now; dr = dbCommand.ExecuteReader(); DateTime t2 = DateTime.Now; TimeSpan ts = t2 - t1; int milli = (int)ts.TotalMilliseconds; // milli contains the execution time of dbCommand.ExecuteReader(); Boaz Ben-Porat Show quote "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:49A213DD-227B-4602-81ED-5ADF4E32687E@microsoft.com... > Just a guess. > It might be the delay in creating and opening the connection. > Why don't you log the current time just before calling the SP and after it > and find the time difference. That can narrow down on what the issue is. > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > > > > "Boaz Ben-Porat" wrote: > >> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server >> >> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest >> table in the database contains 11,000,000 records. >> >> Framework: .NET 2.0 >> >> I try to run a query against the database, selecting aggregated data from >> views based on the large table. >> When executed from the Query Analizer, it takes 13 seconds. >> When executed from a .NET application, it takes 140 seconds. >> >> The database is well tuned (or else the query analizer would go slowly), >> so >> I can't find the reason for this difference. >> >> Any suggestion ? >> >> TIA >> Boaz Ben-Porat >> >> Milestone Systems >> >> >> >> Boaz ,shalom
Do you open connection just before calling the view? Can you post a piece of code how you did it? Have you looked at execution plan of the query? Does the optimizer available to use indexes? Show quote "Boaz Ben-Porat" <b**@milestone.dk> wrote in message news:u0WLwzelGHA.4980@TK2MSFTNGP05.phx.gbl... > Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server > > database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest > table in the database contains 11,000,000 records. > > Framework: .NET 2.0 > > I try to run a query against the database, selecting aggregated data from > views based on the large table. > When executed from the Query Analizer, it takes 13 seconds. > When executed from a .NET application, it takes 140 seconds. > > The database is well tuned (or else the query analizer would go slowly), > so I can't find the reason for this difference. > > Any suggestion ? > > TIA > Boaz Ben-Porat > > Milestone Systems > > > Shalom Uri
The code is attached in BadSql.cpp. 1. Yes, I open a connection just before calling the view, but the time to open is not included in execution time. 2. I run the same query, with the same parameters from the Query Analyzer. The execution time was 13-15 seconds and the execution plan used the right indexes. When the query is called from a .NET application (.NET 2.0 , C#), it takes 140-140 seconds. It looks like a problem with .NET communication with the database. Thanks Boaz Ben-Porat Milestone systems Denmark Tel.: (+45) 88 300 325 Mobile: (+45) 25606725 Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message [attached file: BadSql.cpp]news:%23JM7UFflGHA.4244@TK2MSFTNGP02.phx.gbl... > Boaz ,shalom > > Do you open connection just before calling the view? Can you post a piece > of > code how you did it? > Have you looked at execution plan of the query? Does the optimizer > available > to use indexes? > > > > "Boaz Ben-Porat" <b**@milestone.dk> wrote in message > news:u0WLwzelGHA.4980@TK2MSFTNGP05.phx.gbl... >> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server >> >> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest >> table in the database contains 11,000,000 records. >> >> Framework: .NET 2.0 >> >> I try to run a query against the database, selecting aggregated data from >> views based on the large table. >> When executed from the Query Analizer, it takes 13 seconds. >> When executed from a .NET application, it takes 140 seconds. >> >> The database is well tuned (or else the query analizer would go slowly), >> so I can't find the reason for this difference. >> >> Any suggestion ? >> >> TIA >> Boaz Ben-Porat >> >> Milestone Systems >> >> >> > > Boaz
Hmm, I don't know, try running DBCC FREEPROCCACHE and UPDATE STATISTICS on the server to see if this helps BTW, are you in Denmark right now? Show quote "Boaz Ben-Porat" <b**@milestone.dk> wrote in message news:uJtp7nflGHA.3588@TK2MSFTNGP02.phx.gbl... > Shalom Uri > > The code is attached in BadSql.cpp. > > 1. Yes, I open a connection just before calling the view, but the time to > open is not included in execution time. > > 2. I run the same query, with the same parameters from the Query Analyzer. > The execution time was 13-15 seconds > and the execution plan used the right indexes. When the query is called > from a .NET application (.NET 2.0 , C#), > it takes 140-140 seconds. It looks like a problem with .NET > communication with the database. > > Thanks > Boaz Ben-Porat > Milestone systems > Denmark > > Tel.: (+45) 88 300 325 > Mobile: (+45) 25606725 > > > > > > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:%23JM7UFflGHA.4244@TK2MSFTNGP02.phx.gbl... >> Boaz ,shalom >> >> Do you open connection just before calling the view? Can you post a piece >> of >> code how you did it? >> Have you looked at execution plan of the query? Does the optimizer >> available >> to use indexes? >> >> >> >> "Boaz Ben-Porat" <b**@milestone.dk> wrote in message >> news:u0WLwzelGHA.4980@TK2MSFTNGP05.phx.gbl... >>> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server >>> >>> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest >>> table in the database contains 11,000,000 records. >>> >>> Framework: .NET 2.0 >>> >>> I try to run a query against the database, selecting aggregated data >>> from >>> views based on the large table. >>> When executed from the Query Analizer, it takes 13 seconds. >>> When executed from a .NET application, it takes 140 seconds. >>> >>> The database is well tuned (or else the query analizer would go slowly), >>> so I can't find the reason for this difference. >>> >>> Any suggestion ? >>> >>> TIA >>> Boaz Ben-Porat >>> >>> Milestone Systems >>> >>> >>> >> >> > > > Boaz:
I could not view your code in the CPP file. However, you can try wrapping the query in a stored procedure and calling the procedure from C#. Hope this helps. Thanks Uri Dimant wrote: Show quote > Boaz > > Hmm, I don't know, try running DBCC FREEPROCCACHE and UPDATE STATISTICS > on the server to see if this helps > > BTW, are you in Denmark right now? > > > > The database is well tuned (or else the query analizer would go slowly), Are you running the *exact* same command text in QA as the application code? > so I can't find the reason for this difference. Literals vs. parameters can make a big difference in the execution plan with some types of queries. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Boaz Ben-Porat" <b**@milestone.dk> wrote in message news:u0WLwzelGHA.4980@TK2MSFTNGP05.phx.gbl... > Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server > > database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest > table in the database contains 11,000,000 records. > > Framework: .NET 2.0 > > I try to run a query against the database, selecting aggregated data from > views based on the large table. > When executed from the Query Analizer, it takes 13 seconds. > When executed from a .NET application, it takes 140 seconds. > > The database is well tuned (or else the query analizer would go slowly), > so I can't find the reason for this difference. > > Any suggestion ? > > TIA > Boaz Ben-Porat > > Milestone Systems > > > Yes, I use parameters in both cases. Here is the Query Analyzer call:
declare @startdate datetime declare @enddate datetime set @startdate = '2006-05-16 00:00:00' set @enddate = '2006-06-16 23:59:59' SELECT top 2000 dbo.CardTender.CardNumber AS [Card Number], COUNT(*) AS [Card Usage Count] FROM dbo.CardTender WHERE (dbo.CardTender.TranDate BETWEEN @StartDate AND @EndDate) GROUP BY dbo.CardTender.CardNumber ORDER BY COUNT(*) DESC Runs in a reasonable time (13-15 seconds). Thanks Boaz Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:uuQjIrflGHA.2304@TK2MSFTNGP02.phx.gbl... >> The database is well tuned (or else the query analizer would go slowly), >> so I can't find the reason for this difference. > > Are you running the *exact* same command text in QA as the application > code? Literals vs. parameters can make a big difference in the execution > plan with some types of queries. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Boaz Ben-Porat" <b**@milestone.dk> wrote in message > news:u0WLwzelGHA.4980@TK2MSFTNGP05.phx.gbl... >> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server >> >> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest >> table in the database contains 11,000,000 records. >> >> Framework: .NET 2.0 >> >> I try to run a query against the database, selecting aggregated data from >> views based on the large table. >> When executed from the Query Analizer, it takes 13 seconds. >> When executed from a .NET application, it takes 140 seconds. >> >> The database is well tuned (or else the query analizer would go slowly), >> so I can't find the reason for this difference. >> >> Any suggestion ? >> >> TIA >> Boaz Ben-Porat >> >> Milestone Systems >> >> >> > > I didn't see anything obvious in the query/code you posted. What's the
underlying type of DbCommand and what provider are you using? Data caching could be an issue since it looks like the query will require a scan of a lot of data. Do you get about the same times if you run the query via the application twice in a row? This is an OT ovservation but I noticed you are using BETWEEN for the datetime range. You might consider reformulating the expression to be exclusive of the end date so that values between 23:59:59 and midnight will also be included. set @startdate = '20060516' set @enddate = '20060617' SELECT TOP 2000 dbo.CardTender.CardNumber AS [Card Number], COUNT(*) AS [Card Usage Count] FROM dbo.CardTender WHERE dbo.CardTender.TranDate >= @StartDate AND dbo.CardTender.TranDate < @EndDate GROUP BY dbo.CardTender.CardNumber ORDER BY COUNT(*) DESC -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Boaz Ben-Porat" <b**@milestone.dk> wrote in message news:uyL7wBglGHA.1832@TK2MSFTNGP04.phx.gbl... > Yes, I use parameters in both cases. Here is the Query Analyzer call: > > > declare @startdate datetime > declare @enddate datetime > > set @startdate = '2006-05-16 00:00:00' > set @enddate = '2006-06-16 23:59:59' > > > SELECT > top 2000 dbo.CardTender.CardNumber AS [Card Number], COUNT(*) AS [Card > Usage Count] > FROM dbo.CardTender > WHERE (dbo.CardTender.TranDate BETWEEN @StartDate AND @EndDate) > GROUP BY dbo.CardTender.CardNumber > ORDER BY COUNT(*) DESC > > Runs in a reasonable time (13-15 seconds). > > Thanks Boaz > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:uuQjIrflGHA.2304@TK2MSFTNGP02.phx.gbl... >>> The database is well tuned (or else the query analizer would go slowly), >>> so I can't find the reason for this difference. >> >> Are you running the *exact* same command text in QA as the application >> code? Literals vs. parameters can make a big difference in the execution >> plan with some types of queries. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Boaz Ben-Porat" <b**@milestone.dk> wrote in message >> news:u0WLwzelGHA.4980@TK2MSFTNGP05.phx.gbl... >>> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server >>> >>> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest >>> table in the database contains 11,000,000 records. >>> >>> Framework: .NET 2.0 >>> >>> I try to run a query against the database, selecting aggregated data >>> from views based on the large table. >>> When executed from the Query Analizer, it takes 13 seconds. >>> When executed from a .NET application, it takes 140 seconds. >>> >>> The database is well tuned (or else the query analizer would go slowly), >>> so I can't find the reason for this difference. >>> >>> Any suggestion ? >>> >>> TIA >>> Boaz Ben-Porat >>> >>> Milestone Systems >>> >>> >>> >> >> > > Thanks to all those who answerd. Sorry I botherd you. The error is not
linked to Sql Server but to a COM component that is stucked in the way. A soloution is found. /Boaz Show quote "Boaz Ben-Porat" <b**@milestone.dk> wrote in message news:u0WLwzelGHA.4980@TK2MSFTNGP05.phx.gbl... > Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server > > database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest > table in the database contains 11,000,000 records. > > Framework: .NET 2.0 > > I try to run a query against the database, selecting aggregated data from > views based on the large table. > When executed from the Query Analizer, it takes 13 seconds. > When executed from a .NET application, it takes 140 seconds. > > The database is well tuned (or else the query analizer would go slowly), > so I can't find the reason for this difference. > > Any suggestion ? > > TIA > Boaz Ben-Porat > > Milestone Systems > > >  |
|||||||||||||||||||||||
Other interesting topics