March 3, 2009 at 4:28 pm
Hi anyone
Need some help with a query that won’t run when the records get too many.
See below qrySumLostHrsForRLVJobsRanked2, this query runs very quickly for both Campaigns parameters i.e. "C12009-2204" and "C12009-467", however the next query in the set qrySumLostHrsForRLVduring3test run in a few seconds for C12009-2204 but wont run at all for C12009-467, PC processing usage is at 100% for both campaigns, however C12009-467 hmmm well I have waited for up to 4 min for the query to calculate.
Does anyone have any better idea how to build the query so I get the same result faster?
Needless to say there are a couple more queries that go before this but its qrySumLostHrsForRLVduring3test that’s causing the problem.
Thanks for any help
Asta
qrySumLostHrsForRLVJobsRanked2
autoNumCampaign:Job Ref No.:TotalLostHrsStartmyDateAsNumRank
33C12009-220405/02/2009 03:00:002009020503002
34C12009-220409/02/2009 07:00:002009020907003
35C12009-220414/02/2009 10:00:002009021410004
143C12009-22041431214/02/2009 23:00:002009021423005
159C12009-220418/02/2009 07:30:002009021807306
160C12009-22041607223/02/2009 03:28:002009022303287
189C12009-220418913.501/03/2009 20:00:002009030120008
264C12009-220421/01/2009 19:30:002009012119301
qrySumLostHrsForRLVduring3test
Job Ref No.:A.RankB.RankC.RankautoNumCampaign:
32134C12009-2204
43235C12009-2204
654159C12009-2204
143543143C12009-2204
160765160C12009-2204
189876189C12009-2204
SQL: qrySumLostHrsForRLVduring3test
SELECT A.JobRefNo, A.autoNum, A.Rank, B.Rank, C.Rank, A.CampaignNo
FROM qrySumLostHrsForRLVJobsRanked2 AS A, qrySumLostHrsForRLVJobsRanked2 AS B, qrySumLostHrsForRLVJobsRanked2 AS C
WHERE (((B.Rank)=[A].[Rank]-1) AND ((C.Rank)=[A].[Rank]-2))
ORDER BY A.JobRefNo, A.Rank;
qrySumLostHrsForRLVJobsRanked2
autoNumCampaign:Job Ref No.:TotalLostHrsStartmyDateAsNumRank
36C12009-467364.504/01/2009 01:00:002009010401001
37C12009-46705/01/2009 05:00:002009010505002
38C12009-46738306/01/2009 07:30:002009010607303
39C12009-46739307/01/2009 03:00:002009010703004
40C12009-46708/01/2009 01:45:002009010801455
42C12009-46709/01/2009 00:30:002009010900306
43C12009-46743911/01/2009 04:00:002009011104007
44C12009-46711/01/2009 21:00:002009011121008
45C12009-46712/01/2009 20:00:002009011220009
46C12009-46713/01/2009 09:25:0020090113092510
48C12009-46714/01/2009 19:30:0020090114193011
49C12009-467491.515/01/2009 16:00:0020090115160012
50C12009-46717/01/2009 09:00:0020090117090013
51C12009-46718/01/2009 08:00:0020090118080014
52C12009-46719/01/2009 10:00:0020090119100015
54C12009-46720/01/2009 19:00:0020090120190016
55C12009-46721/01/2009 12:30:0020090121123017
56C12009-46756422/01/2009 23:30:0020090122233018
57C12009-46724/01/2009 17:30:0020090124173019
58C12009-46724/01/2009 23:30:0020090124233020
60C12009-46726/01/2009 07:00:0020090126070021
61C12009-467611228/01/2009 13:30:0020090128133022
62C12009-46762829/01/2009 19:30:0020090129193023
63C12009-46730/01/2009 10:30:0020090130103024
64C12009-46731/01/2009 19:30:0020090131193025
66C12009-467661203/02/2009 20:00:0020090203200026
67C12009-46704/02/2009 19:15:0020090204191527
100C12009-46705/02/2009 19:30:0020090205193028
101C12009-46706/02/2009 14:10:0020090206141029
102C12009-467102807/02/2009 20:30:0020090207203030
104C12009-4671041210/02/2009 19:00:0020090210190031
105C12009-467105811/02/2009 10:50:0020090211105032
106C12009-467106312/02/2009 11:30:0020090212113033
107C12009-46713/02/2009 08:00:0020090213080034
108C12009-467108314/02/2009 23:00:0020090214230035
110C12009-46715/02/2009 22:15:0020090215221536
111C12009-46716/02/2009 16:00:0020090216160037
112C12009-46717/02/2009 07:00:0020090217070038
113C12009-46718/02/2009 00:40:0020090218004039
114C12009-46718/02/2009 22:00:0020090218220040
116C12009-46719/02/2009 11:00:0020090219110041
117C12009-46720/02/2009 07:30:0020090220073042
118C12009-46720/02/2009 23:15:0020090220231543
119C12009-4671191121/02/2009 15:00:0020090221150044
120C12009-4671202822/02/2009 20:00:0020090222200045
122C12009-46726/02/2009 07:45:0020090226074546
123C12009-467123327/02/2009 07:30:0020090227073047
124C12009-46728/02/2009 07:30:0020090228073048
125C12009-46701/03/2009 02:40:0020090301024049
126C12009-467126601/03/2009 19:30:0020090301193050
286C12009-46702/03/2009 19:30:0020090302193051
March 4, 2009 at 12:55 am
My Qs:
1. It seems you have forgotten to include the first query (you are refereing to two queries in your question).
2. How many records are in the table?
3. Have you looked at the execution plan?
4. Are there any indexes on the Table?
Tips:
1. Try adding an Index on the Rank column (of course, make sure to cover necessary columns)
2. Try experimenting with correlated sub-queries for B and C (instead of joining)
HTH.
March 4, 2009 at 12:58 am
Oh, sorry, forgot to include the first question I wanted to ask -
0. What are you trying to get out of the query? You don't even seem to be selecting anything from B and C other than the Rank column.
March 4, 2009 at 1:10 am
Can you post the table definitions, index definitions and execution plan (saved as a.sqlplan file, zipped and attached) please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 2:28 am
Hi
Thank you for all your help, I have attached a word document that shows all the queries and the two tables used in the queries + showed which fields are indexed.
I have included the extra coloumns in the last table, I took them out the first time to save confusing, but basically the overall aim is to work on a 3 record cycle, you can see in the final query that the first, second and third record LostHrs are being summed. I’m not sure what a execution plan is or how to find one or save on in Access. So I have made an image of the tables and typed whether or not they are indexed. In the query set I’m doing the following, finding the sum of lost hrs for each job, I have to rank the records based on the start date as I cant be sure the jobs are entered in sequence. So I convert my start date and time to a number then rank this number which I can then use in my final query to make sure I get record 1,2,3 : 2, 3,4 : 3,4,5 and so on. I’m think this line of SQL is not helping. Notice I was able to run the query I had 15 ranked jobs, when I run it with a different parameter one that has 51 ranked jobs the computer crashes :-(. This query is supposed to work with over 400 ranked jobs.
Thank for you help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply