Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jp2code
Posting Yak Master
175 Posts |
Posted - 2009-03-02 : 11:49:15
|
I've got a query I wrote that runs in Visual Studio 2005 and produces the results I want, but it takes 6 seconds to run this query.Everything pulls from a single table that does not have a primary key (horrible design which will be modified ...one day).The query returns in 1 second or less unless I add the extra CASE statement in the Sequence Number field (Seq#) to match the sequence number of the tubing fields (which do not include any data) to those of their parent label fields.Could someone please show me how to speed this routine up? 6 Seconds is a very long lag when scrolling through the parts in our database viewer.Here's my query:Declare @WorkOrder_Number char(10)Set @WorkOrder_Number='xr1200'SELECT Op_ID AS 'Badge', System_ID AS 'Station', Date_Time AS 'Date', Serial_Number AS 'Serial Number',CASE WHEN System_ID Like '%Label%' THEN WorkOrder_Seq ELSE (SELECT WorkOrder_Seq FROM ACP_Parts API WHERE API.Serial_Number=APO.Serial_Number AND System_ID Like '%Label%') END AS 'Seq#',Test_Result AS 'Status Result'FROM ACP_Parts APO WHERE Serial_Number IN ( SELECT Serial_Number FROM ACP_Parts WHERE (WorkOrder_Number=@WorkOrder_Number))ORDER BY Serial_Number, System_ID Here's the table this query produces, which is the format we would like, it just takes a long time to load:007135 Debug_Label_09 2009-02-25 16:07:05.460 C710297 1000 09 1 W.O.XR1200 R4195J,Grp4,RD4,TH71,Cond/SubCooler,650psi007135 Debug_Tubing_09 2009-03-02 10:07:20.490 C710297 1000 09 1 Tubing Complete007135 Debug_Label_09 2009-02-25 16:20:31.813 C710297 1001 09 2 W.O.XR1200 R4195J,Grp4,RD4,TH71,Cond/SubCooler,650psi007135 Debug_Tubing_09 2009-03-02 10:07:51.710 C710297 1001 09 2 Tubing Complete007135 Debug_Label_09 2009-02-24 15:54:09.347 C710297 1002 09 0 W.O.XR1200 R4195J,Grp4,RD4,TH71,Cond/SubCooler,650psi007135 Debug_Label_09 2009-02-27 15:33:48.203 C710297 1003 09 0 W.O.XR1200 R4195J,Grp4,RD4,TH71,Cond/SubCooler,650psi007135 Debug_Label_09 2009-02-27 15:33:48.203 C710297 1004 09 0 W.O.XR1200 R4195J,Grp4,RD4,TH71,Cond/SubCooler,650psi007135 Debug_Label_09 2009-02-27 16:36:16.440 C710297 1028 09 0 W.O.XR1200 R4195J,Grp4,RD4,TH71,Cond/SubCooler,650psiAlso, how do I show tabular data? The Forum Code [table] does not work. Avoid Sears Home Improvement |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-02 : 12:08:49
|
It's [ code ] and [ /code ].Without spaces in the tags. E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-02 : 12:19:53
|
Try thisDECLARE @WorkOrder_Number VARCHAR(10)SET @WorkOrder_Number = 'xr1200'SELECT apo.Op_ID AS Badge, apo.System_ID AS Station, apo.Date_Time AS [Date], apo.Serial_Number AS [Serial Number], doh.WorkOrder_Seq AS [Seq#], apo.Test_Result AS [Status Result]FROM ACP_Parts AS apoINNER JOIN ( SELECT Serial_Number, MAX(CASE WHEN System_ID LIKE '%Label%' THEN WorkOrder_Seq ELSE NULL END) AS WorkOrder_Seq FROM ACP_Parts WHERE WorkOrder_Number = @WorkOrder_Number GROUP BY Serial_Number ) AS doh ON doh.Serial_Number = apo.Serial_NumberORDER BY apo.Serial_Number, apo.System_ID E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-02 : 12:23:10
|
5 seconds huh?Try my suggestion and post back results. E 12°55'05.63"N 56°04'39.26" |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2009-03-02 : 12:32:01
|
Wow! 1 (ONE) second! Fantastic!I saw that original post where you told me to use the [ code ] keyword, and thought, "Great. Now everyone is going to think my problem has been resolved, and no one will look at it."I'm glad you weren't done yet! :) Avoid Sears Home Improvement |
|
|
|
|
|
|
|