Query Optimisation

I have a query which is rather crude and runs exponentially slower the larger the input.

My sp is as follows:

CREATE PROCEDURE sp_TransactionStatistics#Get

@TransactionIDList Text

AS

Select TransactionID, Printed From tbl_Transaction_Statistics Where PatIndex ('%' + RTrim(TransactionID) + '%', @TransactionIDList) > 0 And Printed = 1

GO

I am passing values into TransactionIDList  such as:

'0910418092,0910420786,0910431792,0910431792'

This works fine with smallish numbers but as the array grows large it gets very slow.

tbl_Transaction_Statistics looks like this

TransactionID      Printed      PrintDateTime

0910270508      1      2005-03-17 17:04:01.450
0910271408      1      2005-03-17 17:04:01.433
0910272402      1      2005-03-17 17:04:01.433
0910272403      1      2005-03-17 17:04:01.433
0910272839      1      2005-03-17 17:04:01.450
0910273307      1      2005-03-17 17:04:01.450
0910273321      1      2005-03-17 17:04:01.450
0910274523      1      2005-03-17 17:04:01.467
0910274547      1      2005-03-17 17:04:01.467
0910275541      1      2005-03-17 17:04:01.450

I am looking for a different/better way to design this query so it runs fast.  The largest number of csv values being passed in is about 900

Thanks

Dave

PS - @TransactionIDList needs to be text as this sometimes goes over 8000 chars
LVL 19
Who is Participating?

Commented:
it's definitily more complex because your param is > 8000 chars, but it is doable.
The first step you need to do in your proc is to break down the text into varchars.
Then you can processes each of these in turn, parsing out the individual IDs into a table.
Then you can run your query to join against this table. Something like this :

CREATE PROCEDURE sp_TransactionStatistics(@TransactionIDList text)
AS

begin
declare @st integer
declare @end integer

declare @transactionID varchar(10)
declare @TransactionIDListTable table(id integer identity(1,1),TransactionIDList varchar(7000))
declare @TransactionIDListVC varchar(7000)
declare @transactions table(transactionID varchar(10))
declare @IDPtr integer
set @st = 1

set @transactionIDListVC = substring(@TransactionIDList,@st,7000)
while @transactionIDListVC <> ''
begin
insert into @TransactionIDListTable(TransactionIDList) values (@TransactionIDListVC)
set @st = @st + 7000
set @TransactionIDListVC = substring(@TransactionIDList,@st,7000)
end

select @TransactionIDListVC = TransactionIDList from @TransactionIDListTable where id = 1

set @idptr = 1
set @st = 1

set @end = charindex(',',@TransactionIDListVC,@st+1)
while @end > 0
begin
select @st,@end
set @transactionID = substring(@TransactionIDListVC,@st,@end-@st)
insert into @transactions values(@transactionID)
set @st = @end + 1
set @end = charindex(',',@TransactionIDListVC,@st)
if @end <= 0
begin   --- hit the end of this string, get the next one if it exists...
if exists (select 1 from @TransactionIDListTable where id = @idptr + 1)
begin
set @idptr = @idptr + 1
select @TransactionIDListVC = substring(@TransactionIDListVC,@st,8000) + TransactionIDList from @TransactionIDListTable where id = @idptr
set @st = 1
set @end = charindex(',',@TransactionIDListVC,@st+1)
end
end

end
set @transactionID = substring(@TransactionIDListVC,@st,8000)
insert into @transactions values(@transactionID)

Select TransactionID, Printed From tbl_Transaction_Statistics Where TransactionID in (select TransactionID from @transactions)

end
0

Commented:
Have you tried inserting your transaction ID's into a table and then running a Comparison query? For large numbers this may be faster.

something like

create table called searchentries with field transactionID
index on transactionid

insert into searchentries(0910418092)
insert into searchentries(0910420786)

Select TransactionID, Printed From tbl_Transaction_Statistics Where TransactionID In (SELECT TransactionID FROM SearchEntries) And Printed = 1

This is only a guess but might be worth a try.

I presume you do have an index on TransactionID

regards

0

Author Commented:
Ok I'll give this a try and get back to you

Thanks

Dave
0

Author Commented:
I had this problem a while ago and I just remembered that I tried this already.  I think the problem was that I have SQL code to iterate through a passed in array to create the Insert statements.  Problem is that this doesn't work with text values and my array needs to be text as it can contain more than 8000 chars.

Hope you understand what I mean!

Any other ideas?

Dave
0

Commented:
I was suggesting that you dont use the massive array, just do a series of inserts into the search table, simple inserts are very quick whereas iterations and pattern matching can take some time.

0

Author Commented:
Ok well this is an ASP application so how are you suggesting I do the inserts?

1 call to a sp passing in array
Thousands of calls to a sp passing in 1 value at a time.
Creating 1 massive SQL text in my client app  and runnging that eg:

Inert.....; Insert....; Isert

Also I have found that if I can create dynamic SQL this runs quite fast eg:

Set SQLText = 'Select TransactionID, Printed From tbl_Transaction_Statistics Where TransactionID In (' + @TransactionIDList  + ')'

Problem again is that this won't work if @TransactionIDList is text

I tried puting this into my client app and not using a sp and this did work and get my code (including other non SQL code) to run at 50 seconds instead of 70 but I'm still losing a lot of performance due to not using a sp
0

Commented:
I'm suggesting that if you have the search numbers individually in the first place then add them to a search table one by one, might not seem very efficient but may be a lot faster

0

Commented:
Ah now dave, you didn't say it was ASP
0

Author Commented:
No sorry, didn't think it wasn't really relevant that it was ASP until we start looking at not using a sp.

0

Commented:
Ok well you said you had the code to parse the array into insert statements.
dont forget that although varchars are limited to 8000, since you know that your max will

The largest number of csv values being passed in is about 900
i.e.9000 characters why not just use 2 input params, splitting the list into 2

then go from there
0

Commented:
as I showed in my stored proc, you can split out a text string but it is a bit messy. Although it will take << 1 sec to run, so performance-wise it should not be an issue. If you want the flexibility to pass in  the parameters as a comma delimited string, then it should work OK.
0

Author Commented:
Ok thanks guys.  I have a few things to try out here and will do so and get back to you, probably in a few days as just got very busy here!

I will be back though.....

Thanks

Dave
0

Author Commented:
Hi

Can you not classify this question as abandoned please. I am currently coding some of the sugestions made here (as I said in my last post) and will be awarding points soon.

Thanks

Dave
0

Author Commented:
Ok this is waht I did - split my input data into several varchars, then parsed them in the sp, inserted into a temp table and then did a join with this.  The improvement in performance was down from 16 seconds I think to a fraction of 1 second.

Thanks for everyone's help

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.