Select ID slower than Select * | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Select ID slower than Select *

I am trying to tune a query. When I run the first one in Query Analyzer it returns in less than one second while the Select Id takes between 15 and 30 seconds to return. I ran DBCC REINDEX on the OrderMaster table. vwOrders is a view of the OrderMaster table. Here are the two queries. select * from MMS_DATAMODULES.dbo.vwOrders where invoicenumber like ‘post fund v1074/%’ –<1 sec
select id from MMS_DATAMODULES.dbo.vwOrders where invoicenumber like ‘post fund v1074/%’ –between 15 and 30 sec.
Run execution plan for both selects, and see indexs used in each one. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
here are the results from the execution plan.<br /><br />StmtText <br />—————————————————————————————– <br />select * from MMS_DATAMODULES.dbo.vwOrders where invoicenumber like ‘post fund v1074/%'<br /><br />(1 row(s) affected)<br /><br />StmtText <br />——————————————————————————————————————————————————————————————————————————————————— <br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1010]=rtrim([Person].[FirstName])+’ ‘+ltrim([Person].[LastName]), [Expr1011]=rtrim([Person].[FirstName])+’ ‘+ltrim([Person].[LastName]), [Expr1012]=[OrderMaster].[CALC_GrandTotal]-[OrderMaster].[CALC_PaymentTotal]))<br /> |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Company].[ID])=([OrderMaster].[BillToCompanyID]))<br /> |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Company].[IDX_Company_2_Name]))<br /> |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Company].[ID])=([OrderMaster].[ShipToCompanyID]))<br /> |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Company].[IDX_Company_2_Name]))<br /> |–Nested Loops(Inner Join)<br /> |–Nested Loops(Inner Join)<br /> | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1000]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[OrderMaster]) WITH PREFETCH)<br /> | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />like([OrderMaster].[InvoiceNumber], ‘post fund v1074/%’)))<br /> | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[OrderMaster].[IDX_Orders_InvoiceNumber]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[InvoiceNumber] &gt;= ‘post fund v1074/’ AND [OrderMaster].[InvoiceNumber] &lt; ‘post fund v1074:’) ORDERED)<br /> | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Person].[IDX_PersonMatch]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Person].[ID]=[OrderMaster].[BillToID]) ORDERED)<br /> |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Person].[IDX_PersonMatch]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Person].[ID]=[OrderMaster].[ShipToID]) ORDERED)<br /><br />(12 row(s) affected)<br /><br />StmtText <br />——————————————————————————————– <br /><br />select id from MMS_DATAMODULES.dbo.vwOrders where invoicenumber like ‘post fund v1074/%'<br /><br />(1 row(s) affected)<br /><br />StmtText <br />—————————————————————————————————————————————————————————————————————————————————————- <br /> |–Parallelism(Gather Streams)<br /> |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Company].[ID])=([OrderMaster].[BillToCompanyID]))<br /> |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Company].[ID]))<br /> | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Company].[IDX_Company_57_GLOrderLev]))<br /> |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[BillToCompanyID]))<br /> |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Company].[ID])=([OrderMaster].[ShipToCompanyID]))<br /> |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Company].[ID]))<br /> | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Company].[IDX_Company_57_GLOrderLev]))<br /> |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[ShipToCompanyID]))<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[ShipToID])=([Person].[ID]))<br /> |–Parallelism(Repartition Streams, PART ITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[ShipToID]))<br /> | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[BillToID])=([Person].[ID]))<br /> | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[BillToID]))<br /> | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bmk1000]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[OrderMaster]) WITH PREFETCH)<br /> | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />like([OrderMaster].[InvoiceNumber], ‘post fund v1074/%’)))<br /> | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[OrderMaster].[IDX_Orders_InvoiceNumber]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[OrderMaster].[InvoiceNumber] &gt;= ‘post fund v1074/’ AND [OrderMaster].[InvoiceNumber] &lt; ‘post fund v1074:’) ORDERED)<br /> | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Person].[ID]))<br /> | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Person].[idx_mailcode]))<br /> |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Person].[ID]))<br /> |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[MMS].[dbo].[Person].[idx_mailcode]))<br /><br />(20 row(s) affected)<br /><br />
I wish I had a solution, but I’ve been noticing that weird issue with parallelism popping up in my .NET application as well. While the slow query is running, the process will split into 8-20 contexts and take forever, while disk and processor activity on the machine spikes. The really bizarre part is that after restarting the SQL service, the query plan no longer includes all the parallelism, and the performance goes back to normal. I also have an issue in another .NET application where the performance spikes in a similar way, but without the parallel contexts. If I run the query in Query Analyzer, the results come back in less than a second, versus 2-3 minutes from the .NET application. I’ve even tried pulling the query directly from Profiler to try outside the application, but then it runs normally. It’s not a server I can restart immediately so I don’t know yet if the problem will go away. I don’t mean to hijack the thread, but when I saw the parallelism in your query plan, it looked like a problem very similar to mine.
Have you noticed in Profiler if these are running as SP:Completed or RPC:Completed? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Yes, there are Starting and Completed events for both RPC and SP, starting and ending at the same time.
Are you using Application roles on SQL server?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenet.asp for your information on improving performance for .NET application. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
sely, when it’s running bad in .NET run it as a normal execute from Query Analyzer. Next, run is wrapped in an sp_executesql. See if it producest he slow runtime issue you are having in .NET. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Nope, it runs just as fast inside sp_executesql.
satya, I’m not exactly sure what you’re recommending. However, I’ve isolated the problem to SQL specifically. The procedure I’m calling executes one of four queries, depending on which of two parameters are present, instead of building a dynamic query. If i change the procedure to specifically use the one query for the parameters I’m testing with, or if i build a query dynamically, the results come back to the application instantaneously. This could explain why it’s only this procedure that’s having issues as well. Now, I’m far from a DBA, but this brings me back to parallelism, because doesn’t it start executing all the queries in nested conditionals immediately, in parallel? In my original post, I mentioned the explicit parallelism problem I had, where extra contexts were showing up. The contexts were created only when the procedure was called by my .NET web application, not a direct call from Query Analyzer, and the performance was worse with all the contexts. So I guess the temporary solution is to use a dynamic query. Why would this problem be coming up though, especially only when being called by my application?
I think you have a problem with your data connection component. We run .NET all the time and I’ve never, ever seen this behaviour. And it’s not SQL if you can’t reproduce the problem without .NET. It’s .NET. That just makes sense doesn’t it? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
One weird thing that I came across was that rebuilding the stored procedure seemed to make the problem go away. I’d replaced the stored procedure with a dynamic query version to fix the problem, but then in doing some follow up testing, I created a new copy of the original problem and it didn’t have any issues. I switched the main procedure that the application uses back to the original, and it was working fine again. If the issue surfaces again, I’ll see if recreating the stored procedure fixes anything.<br /><br />Although, I understand what you’re saying about .NET and it’s definitely suspicious. The problem is consistent across client machines though. Everything seems cool now though. Thanks for all the help. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
]]>