| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2010-06-17 : 06:35:27
|
| Hi Folks, hope everyone's wellI've been working on a query that is fairly simple as all the transorming has been done and has been put into several reporting tables. I'm using UNION ALL in each to get all my records. This report will show project information by a utility management company. There are thousands of projects.Anyhow, the main reason I'm asking for help is to do with a parameter I'm using in the WHERE statement. Users are allowed to select either one project i.e. A0000123, or select all projects within a division. If I use DECLARE @ProjectCode varchar(10)SET @ProjectCode = 'A0000123'WHERE ProjectCode = @ProjectCode The query works quite quickly, however, because I want the option to return all, I've usedWHERE (@ProjectCode= '-1' OR ProjectCode = @ProjectCode)Basically saying if it's '-1' then cancel out the parameter and return everything.This takes a long time as SQL server's doing a table scan. This is a real problem. What makes it worse is that it's a string. I've decided to create an integer for each project and use that. I was wondering if anyone had any knowledge of other ways to return all or one record. Or any advice on performance?Thanks for any help in advanceP |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-17 : 07:32:06
|
If you want to retrieve ALL then it must be a scan because there is no decision to take a record or not.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-06-17 : 08:16:56
|
| Thanks for responding webfredIt does a scan when a project is selected for the parameter as in the example code. I'd have thought it would only do the scan if the -1 (all) was selected). Do you know why that would be? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-17 : 08:50:52
|
First ProjectCode should be indexed.Second if Table.ProjectCode is indexed but another data type than the Parameter type then maybe the implicit convert appears and the index can't be used. You should have the Parameter as same data type.But without more information we can't say what is going on.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-17 : 08:51:27
|
| Do you have any indexed column?MadhivananFailing to plan is Planning to fail |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-17 : 08:55:16
|
Also I would try:If @ProjectCode = '-1'begin set @ProjectCode=NULLendin your where clause then:WHERE (@ProjectCode IS NULL OR ProjectCode = @ProjectCode)I believe that performs better than '-1'
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-17 : 09:06:03
|
| http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/--Gail ShawSQL Server MVP |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-17 : 09:18:11
|
Very fine Gail  I've added it to my bookmarks.@cidr: ignore my last post and read what Gail has linked.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-06-17 : 10:48:04
|
Thanks for the help so far.quote:
Gail 06/17/2010http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
I tried this, however it's not working for me. For example, I have statements in my code that use single appostrophes which is causes errors - case when anal_t8 = '0001002 ' then Actual else 0 end as 'decoyTurnover' - Also, I don't know how important it is assign the emtpy string on variable declaration to @Where NVARCHAR(1000) = '' But it's not letting me do at declaration.Does the code have to be a dynamic SQL string? or is there another way round this?________________EDIT 17/06/2010Incidentally, The ProjectCode is indexed and the parameter is the same datatypeI'm not sure what more info I can give as I can break this down to one report table and there's only one parameter i.e. ProjectCode, that I'm testing that's causing the problem________________Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-17 : 11:43:25
|
For each single quote type a second single quote to mask it.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-06-24 : 07:25:49
|
| Hi, Just want to say thanks for all your help. I tried the technique that you have shown me Gail, unfortunately the query is too big to put into a SQL string. I've tried concatenating parameters but that's not allowed, I've tried declaring a parameter to concat the other variables, that still only gives me Nvarchar(4000). If it was SQL2005 I'd be laughing. However, I will be using this code in future so thanks alot for that.Paul:) |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2010-06-24 : 08:51:09
|
| As this query only seems to have two options, why not just have two static queries?If the SELECT part is complicated, it can be put in a VIEW. |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-06-25 : 09:30:33
|
quote:
Ifor 25/06/10As this query only seems to have two options, why not just have two static queries?If the SELECT part is complicated, it can be put in a VIEW.
I'm not sure what you're suggesting here. To build the dataset for my report, there's actually 7 tables with UNION ALL. Each have different parameters. Firstly, to use a dynamic sql I'd probably need to create a sql string for all tables and fit the sql string withe the WHERE statements inbetweenYou mentioned using two static queries. Is it possible you could give me an example of what you mean? I'd apprecait that.:) |
|
|
|
|
Parameter to return one record or all...