| Author |
Topic |
|
smh
Yak Posting Veteran
94 Posts |
 Posted - 2009-05-04 : 17:00:15
|
| I have a long sp that creates a report showing reason for skips between invoice numbers in report data. It is timing out. Several of the routines done on the data take an especially long time and I thought someone might show me a way to speed these up.In the section of the sp I am showing here, I create a table of all the potential invoice numbers between a start and an end number (the start and end numbers are the start and end numbers of the actual records that have been selected for the report.Then I go thru each one of these and see if there really is an invoice number in the data selected for the report. They may all be there, or there may be skips of any number of invoice numbers.The data selected to go into the report is in @reportdatathe @sequence table holds all the potential sequence numbers between the first and last invoice number in @reportdata. The @results table holds all the invoice numbers that are in this sequence but do not have a real record in @reportdata --skipped invoice number. declare @start int declare @end int set @end = 0 set @start = 0 set @start = (select min(InvoiceNumber) from @reportdata) set @end = (select max(InvoiceNumber) from @reportdata) declare @sequence table (InvoiceNumber varchar(20) NULL ) --Generate some sequence data for it SET NOCOUNT ON Declare @i int Set @i = @start WHILE @i <= @end BEGIN Insert Into @sequence (InvoiceNumber ) Values (@i) Set @i = @i + 1 END SET NOCOUNT OFF -- put into @results table the id's in the sequence table that are not in the @reportdata. declare @results table (InvoiceNumber varchar(20) NULL, idType char(6), CustomerName [varchar] (50) NULL ) insert into @results (InvoiceNumber) select a.InvoiceNumber from @sequence a where not exists (select b.InvoiceNumber from @reportdata b where b.InvoiceNumber = a.InvoiceNumber)Finally (there are others things done but if I can shave off some seconds in these routines, I might be ok)There are 2 reasons an invoice number maybe missing from the selected report data. 1. the invoice was deleted so does not exist in the table. 2. the invoice does exist in the table but was not selected for the report.In order to find the latter situation, I put into the @inorder table those invoice numbers that do not exist in the full Order table (tblOrder) or in the @reportdata. declare @inOrder table (InvoiceNumber varchar(20) NULL) insert into @inOrder (InvoiceNumber) select a.InvoiceNumber from tblOrder a where not exists (select b.InvoiceNumber from @reportdata b where b.InvoiceNumber = a.InvoiceNumber)The things I do after this is to update the @results table adding a message as to whether the missing number is skipped or if it is in the Order table and just not selected in the report criteria. I then create the final table with all data for the report.Perhaps the use of "exists" is slowing it down.Perhaps in the last routine with @inorder I am looping thru the larger table and could change it around to have less iterations.Any help on this appreciated.smh |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-05 : 07:18:47
|
Does this work better than the WHERE NOT EXISTS?INSERT INTO @results (InvoiceNumber)SELECT a.[InvoiceNumber]from @sequence a LEFT JOIN @reportData b ON b.[invoiceNumber] = a.[invoiceNumber]WHERE b.[invoiceNumber] IS NULL How much info in the table variables? They are really bad once you get 100's of rows.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-05-05 : 11:31:04
|
| One thing that stands out is you do not have any Primary keys on your table variables. |
|
|
|
smh
Yak Posting Veteran
94 Posts |
Posted - 2009-05-05 : 13:36:12
|
| To be honest. I did not know you could put primary keys on a Table variable. If I do this, what is the best key -- create a identity key or make the invoice varchar the key? |
|
|
|
smh
Yak Posting Veteran
94 Posts |
Posted - 2009-05-05 : 13:52:07
|
| The join change you mentioned instead of 'exists' does not change the timing unfortunately. The problem is that when there are big jumps between the invoice numbers, alot of records are generated. In the situation I am running, there are 290068 records that are in the @results table, i.e. the records that are in sequence but do not exist in the actual data table so this takes alot of time considering the number of loops the entire program goes thru.So if I have the following invoice #'s100000100001...400000400001The routine will generate a @results table with 299,999 records since there is a gap between 100001 and 400000.Then each one in that sequence of missing invoice#'s has to be checked against all the data in the actual table just in case one of these is in the main table but was just not selected for the report. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-05-05 : 13:57:30
|
| ok to start out your problem or bottleneck seems, to me IMHO , the DO WHILE you are doing. Also you might have to use temp tables with index on it if you have such huge numbers. But getting to that point first, why do you have so many records coming back. Are some of these records so old they do not need to be considered. it seems like you could add another filter to exclude some records or do yo need all of them? If you do not need all of them what differentiates the ones you do not need from the ones you need.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
smh
Yak Posting Veteran
94 Posts |
Posted - 2009-05-05 : 14:43:28
|
| You raise an important point about the data. We are using our own test data. While testing a change in a report, we noticed this report was timing out. The reason is, as I mentioned, the large number of nonexistent invoices that have numbers that are within the sequence of invoices collected for the report. It happens that the invoice numbers have large gaps between those that exist and those that do not which is not a question of older invoices necessarily. If I have only two invoices within my report, but there is a gap of 10,000 between the two numbers, then 10,000 numbers between the real two invoices will be generated and then reported as non existent numberes because the user wants to see skips in the invoice and we have to say whether there are real skips or the record was just not selected. And to see the latter situation we have to search for everyone of these sequence numbers to see if there really is an invoice and it just wasn't selected for the report.Now to your point. What is the liklihood that there will ever be such extreme gaps between numbers. Or many records in the report selection with lots of skips -- even if not large skips.Probably not freqently. So one possibility, if putting an index on the table variables does not speed this up that much would be to do a modification of this kind of report to let the user know there are alot of skips but just not to list them all. Or some other variation on that depending on various business considerations of the user.What has happpend up until now is one or two skips exist in the invoice sequence and they get reported with no problem. We just happened upon this situation.. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-05-05 : 14:43:47
|
Yout could try adding a PK to all your table variables like:declare @results table ( InvoiceNumber varchar(20) NOT NULL PRIMARY KEY, idType char(6), CustomerName [varchar] (50) NULL ) If that still is not working try changing the table variables to temp tables (with proper indexes) and see how that fairs.Also, you could look into replacing the loop to populate the sequence table by using a Numbers table (search this site for solutions).One other though.. I'm not 100% sure about getting the records for your 2nd reason (listed above), but is it possible to delete the recorsd from the sequence table once they have been "found" so you don't have to join as many records to the tblOrder table? If so, it *might* speed up teh join to delete the records before the join. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-05-05 : 14:51:16
|
| I agree with you smh and Lamprey. And if there are invoices skipped, honestly how many users will sift through 200,000 records, you will make your users jaded and they will develop a cynical view toward the solutions you provide them. I mean you yourself are frustrated with 200,000 records imagine how the users will feel. And then when it matters most during testing you will haev no feedback from your users (I speak from experience on this one) Best to limit the result set for technical reasons (timeout) and for peoplesoft reasons..200,000 records to show to users is a bit too much if I read you correctly.PKs will not work on table variables.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
smh
Yak Posting Veteran
94 Posts |
Posted - 2009-05-05 : 15:37:52
|
| I don't think the user anticipated -- not did we -- gaps of such magnitude in an invoice table. Usually it is one or two invoices because they were deleted or were not included in the collection.I am going try indexes and see if I can deal in another way in situation where 100 or more invoice numbers do not have an existing record in the report data collection.I can just give a message: "x number invoice numbers in sequence are not in report data please check" I can even list them as a separate report should they want that.I am presuming I can set the timeout differently in ado.net should one want a lengthy report regardless? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-05-05 : 15:58:55
|
| is this real world testing , could this ever happen in the real world. you might be going down a rabbit hole (what's up doc). there is a .CommandTimeOut in ADO and you could pop up a message but I would deal with underlying problem first. indexes will definitely help a whole lot. in your case that is the only option. IMHO<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-06 : 04:40:55
|
| Also -- if you are talking about a large amount of data then even if you put a primary key on a table variable it will still suck.Try a temp table instead -- if there is a lot of data then they can be a huge huge improvement.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
stored proc is timing out -- needs revision