April 16, 2009 at 5:06 am
Hi.
I've got 2 tables, both with PK and secondary indices which match up. Table 1 has 30000 rows. Table 2 has 78 rows.
I've taken the code out of the sp for ease. The sp is passed 5 parameters:
@vendor_names varchar(2000) = '(All)',
@search_criteria varchar(20) = 'All',
@search_type varchar(20) = 'Parent Company',
@start_date datetime = '1900-01-01',
@end_date datetime = '2100-12-31'
@vendor_names is the value of a multi-highlight drop box on a web page. depending on the value of @search_type (can be either "Parent Company" or "Vendor Name"), the drop box will be populated from 1 of 2 different tables. For the purposes of this problem, the other parameters can pretty much be ignored.
The idea is that the user selects a value or values from the drop box and @search _type indicates which table and column to get the values for.
If they specify "(All)" as @vendor_name, we want everything, else just the ones supplied.
Any road up, it runs like a dog. We're talking 2 minutes. If I comment out the "BFO CASE" statement and uncomment the "tv.vendor_name = vrd.vendor_name", it's almost instantaneous.
I need another way of doing that CASE. Nb. we are not allowed to use dynamic SQL.
Here's the code. Any help/suggestions would be greatly appreciated.
Thanks.
declare @vendor_names varchar(2000),
@search_type varchar(20),
@todays_date datetime,
@search_criteria varchar(20),
@start_date datetime,
@end_date datetime
select@vendor_names = 'some_names',
@search_type= 'All',
@todays_date = getdate(),
@search_criteria = 'Vendor_names',
@start_date = '2007-11-01',
@end_date = getdate()
declare @vendor_names_tab table(vendor_name varchar(100) primary key clustered (vendor_name))
-- This just puts a comma separated list into t table variable. No overhead here.
insert into
@vendor_names_tab
select
*
from
dbo.csv_split(@vendor_names,',')
select
vrd.request_id,
vrd.wk_ref,
convert(varchar,vrd.[date],103) as date_recorded,
vrq.request_type,
vrd.add_ref,
vpc.parent_company,
vrd.vendor_name,
vrd.cca,
vrd.litigation,
vr.reason_required,
case vrd.date_received when '1900-01-01' then nullelse convert(varchar,vrd.date_received,103)end as date_received,
datediff(dd,vrd.date_requested,case vrd.date_received when '1900-01-01' then vrd.date_requested else vrd.date_received end) as days_to_receipt,
case vrd.deadline_date when '1900-01-01' then null elseconvert(varchar,vrd.deadline_date,103) end as deadline_date,
case vrd.date_sent when'1900-01-01' then null elseconvert(varchar,vrd.date_sent,103) end as date_sent,
case vrd.incorrect_info_date when '1900-01-01' then null else convert(varchar,vrd.incorrect_info_date,103) end as incorrect_info_date,
convert(varchar,vrd.date_requested,103) as date_requested,
datediff(dd,vrd.date_requested,@todays_date) as days_os_or_recvd,
case vrd.info_unavailable_date when '1900-01-01' then null elseconvert(varchar,vrd.info_unavailable_date,103) end as info_unavailable_date,
vrd.reason_description,
bt.current_balance
from
tbl_drp_vend_requests_data vrd
inner join
tbl_vend_reason vr
on
vr.id = vrd.reason_required
inner join
tbl_vend_requests vrq
on
vrq.id = vrd.reason_required
inner join
tblbigtable bt
on
vrd.wk_ref = bt.wk_ref
inner join
tbl_vend_parent_company vpc
on
vpc.vendor_name = ltrim(rtrim(vrd.vendor_name))
inner join @vendor_names_tab tv
ON
-- BFO CASE
CASE @search_type
WHEN 'Parent Company' THEN vpc.parent_company
ELSE vrd.vendor_name
END
=
CASE tv.vendor_name
WHEN '(All)' THEN
(CASE @search_type
WHEN 'Parent Company' THEN vpc.parent_company
ELSE vrd.vendor_name
END
)
ELSE tv.vendor_name
END
-- end of BFO CASE
--tv.vendor_name = vrd.vendor_name
where
date_sent between @start_date and @end_date
and
no_longer_required = 0
and
isnull(cca,0) = case @search_criteria when 'CCA' then 1 else isnull(cca,0) end
and
isnull(litigation,0) = case @search_criteria when 'Litgation' then 1 else isnull(litigation,0) end
order by
vpc.parent_company asc,
vrd.vendor_name asc,
vrd.date_sent desc
April 16, 2009 at 10:31 am
Hello
First thing I'd do with this is take the switch out of the join:
@search_type = 'Parent Company', tv.vendor_name = '(All)'
- inner join @vendor_names_tab tv ON vpc.parent_company = vpc.parent_company -- CROSS JOIN one row from tv
@search_type = 'Parent Company', tv.vendor_name '(All)'
- inner join @vendor_names_tab tv ON vpc.parent_company = tv.vendor_name
@search_type 'Parent Company', tv.vendor_name = '(All)'
- inner join @vendor_names_tab tv ON vrd.vendor_name = vrd.vendor_name -- CROSS JOIN one row from tv
@search_type 'Parent Company', tv.vendor_name '(All)'
- inner join @vendor_names_tab tv ON vrd.vendor_name = tv.vendor_name
If you look at the second and fourth cases, you want tv.vendor_name to be part of a join condition but you're also using it as a conditional in the CASE. What would happen if the fifth value of ten in the tv table was '(All)'?
Set up tv.vendor_name = '(All)' as a variable, as you have with @search_type.
Instead of using a complex condition to join tv to either vpc or vrd, LEFT JOIN tv to both, then handle filtration in the WHERE clause.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2009 at 3:26 am
First up. thanks for the help.
I hadn't actually considered the possibility that anyone would select "(All)" and something else. Should have seen that.
My Tsql knowledge is rather scant when it comes to the CASE statement (I came from an INGRES background where there's no such thing), so I'm not sure about the correct syntactical usage of your examples. Any further assistance would be greatly appreciated (by both myself and the other members of my team who are all, unbeknown to them, within firing range). 🙂
Thanks.
April 17, 2009 at 4:44 am
Hi David, give the following a try:
SELECT
vrd.request_id,
vrd.wk_ref,
convert(varchar,vrd.[date],103) as date_recorded,
vrq.request_type,
vrd.add_ref,
vpc.parent_company,
vrd.vendor_name,
vrd.cca,
vrd.litigation,
vr.reason_required,
convert(varchar, NULLIF(vrd.date_received, '1900-01-01'), 103) AS date_received,
datediff(dd,vrd.date_requested,case vrd.date_received when '1900-01-01' then vrd.date_requested else vrd.date_received end) as days_to_receipt,
convert(varchar, NULLIF(vrd.deadline_date, '1900-01-01'), 103) AS deadline_date,
convert(varchar, NULLIF(vrd.date_sent, '1900-01-01'), 103) AS date_sent,
convert(varchar, NULLIF(vrd.incorrect_info_date, '1900-01-01'), 103) AS incorrect_info_date,
convert(varchar,vrd.date_requested,103) as date_requested,
datediff(dd,vrd.date_requested,@todays_date) as days_os_or_recvd,
convert(varchar,vrd.info_unavailable_date,103) as info_unavailable_date,
vrd.reason_description,
bt.current_balance
from tbl_drp_vend_requests_data vrd --
inner join tbl_vend_reason vr
on vr.id = vrd.reason_required
inner join tbl_vend_requests vrq
on vrq.id = vrd.reason_required
inner join tblbigtable bt
on vrd.wk_ref = bt.wk_ref
inner join tbl_vend_parent_company vpc --
on vpc.vendor_name = ltrim(rtrim(vrd.vendor_name))
--------------------------------------------------------------------
LEFT JOIN @vendor_names_tab tvpc ON tvpc.vendor_name = vpc.parent_company
LEFT JOIN @vendor_names_tab tvrd ON tvrd.vendor_name = vrd.vendor_name
--------------------------------------------------------------------
WHERE date_sent between @start_date and @end_date
and no_longer_required = 0
and isnull(cca,0) = case @search_criteria when 'CCA' then 1 else isnull(cca,0) end
and isnull(litigation,0) = case @search_criteria when 'Litgation' then 1 else isnull(litigation,0) end
--------------------------------------------------------------------
AND (@vendor_name = '(All)' OR (@search_type = 'Parent Company' AND tvpc.vendor_name IS NOT NULL))
AND (@vendor_name = '(All)' OR (@search_type 'Parent Company' AND tvrd.vendor_name IS NOT NULL))
--------------------------------------------------------------------
ORDER BY vpc.parent_company asc,
vrd.vendor_name asc,
vrd.date_sent desc
You will need a new variable @vendor_name, and I've changed some of the CASEs in the SELECT list, hope that's ok.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2009 at 5:15 am
Chris:
Thanks.
I eclared the new variable and ran it (24 secs) but it didn't erturn any data. Was there anything else I needed to do?
Cheers.
[EDIT]
"declared", even. lol
April 17, 2009 at 5:27 am
Hi David
I'd try it with the following values first:
@vendor_name = '(All)'
@search_type = 'Parent Company'
and if this fails, then deconstruct the two new operations in the WHERE clause, perhaps comment one of them out.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2009 at 6:01 am
David, I reckon there's an error in the WHERE clause - it's challenging writing code with no data to test against - but try this:
WHERE date_sent between @start_date and @end_date
and no_longer_required = 0
AND ((@search_criteria = 'CCA' AND cca = 1) OR 1 = 1)
--and isnull(cca,0) = case @search_criteria when 'CCA' then 1 else isnull(cca,0) end
AND ((@search_criteria = 'Litgation' AND litigation = 1) OR 1 = 1)
--and isnull(litigation,0) = case @search_criteria when 'Litgation' then 1 else isnull(litigation,0) end
--------------------------------------------------------------------
AND ((@vendor_name = '(All)')
OR (@search_type = 'Parent Company' AND tvpc.vendor_name IS NOT NULL)
OR (@search_type 'Parent Company' AND tvrd.vendor_name IS NOT NULL))
You should try it with and without the changes to the existing arguments, which are intended to improve performance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 21, 2009 at 3:54 am
Bum!
Thanks, everyone, for your contributions, but no matter what I try, I cant get this to run in an acceptable time and I've run out of both time and patience.
Here's my solution. It's not big and it's not clever. I hate it. I hate it a lot.
if @search_criteria = 'Parent Company'
select blah from blah where parent_company in @param
else
select blah from blah where vendor_names in in @param
20 years in programming and DBA and I've resorted to this.
NURSE!
April 27, 2009 at 1:10 pm
Using of a CASE shall slow you down. Why not to use UNION ALL instead?
If I understand correctly, there're might be different cases which you can manage in the follow way:
SELECT
FROM my_table
WHERE 1= 1
UNION ALL
SELECT
FROM my_table
WHERE 1 = 0
UNION ALL
SELECT
FROM my_table
WHERE 1 = 2
The result set will return you the first statement or the second one, but not both. If you will build your WHERE close using the right key segments, you will get very fast performance
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply