Solved

setting up select query with use of case statements

Posted on 2013-01-22
4
194 Views
Last Modified: 2013-02-08
Hi,

I have a query which is taking now taking time to run due to a high number of records.  I thought I would be able to use case statement to not only make code simplified but to help with speeding up process.

Do not get how I would set this up because of the if statements dependent on thet user in placing conditions in both the where and from clauses.

Suggestions???
0
Comment
Question by:armanbena
  • 2
4 Comments
 

Author Comment

by:armanbena
ID: 38806372
Sorry, here is file.
0
 

Author Comment

by:armanbena
ID: 38806385
ATTACHMENT
HELP.txt
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 38806639
Hi,

For the sake of progressing the thought I'm assuming SQL 2008.

Take your queries
                        select *
                        from OBI_Project_Editor_Agency_Testing
                        where ((StatusFlag is null) or (StatusFlag = '0'))
                        and lob != 'supple'
                        order by OBI_ASC, GroupNo, GroupNameLong

and run them from SQL Server Management Studio (SSMS).

BUT first select the Actual Execution Plan button.

Look at the plan and respond with the most costly operation.

Look if SQL responds with any hints about missing indexes.

But I can say this:
Your query is non-sargable due to the lob != 'supple', which means that instead of index lookup, at best the query is going to do an index scan instead of a seek.

The other point is that select * is a no-no for performance, and maintainability.
You are telling SQL to return the entire row, so at best SQL once it has identified the rows to return is going to have to refer to the table rather than indexes, maybe a bookmark lookup.

For instance (as a non-sense query)
                        select  StatusFlag
                        from dbo.OBI_Project_Editor_Agency_Testing
                        where ((StatusFlag is null) or (StatusFlag = '0'))

Should refer to just the index on StatusFlag. Now if StatusFlag is a bit, then its not really indexable.

HTH
  David
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 38806649
Rewriting the Coldfusion so it's a bit cleaner based on clients is a separate issue from speeding up the query.  

To improve the speed, you should select on the columns you need; rather than select *.   If possible, resolve the statusFlag to either 0 or NULL so you don't have to use an OR clause (for example, make it NOT NULL and update all NULLs to 0).   Add indexes to some columns as needed.

For cleaning up the code, why not make it more dynamic?  You could put in the user's profiles check boxes or attributes that would drive which where-clause to use.  That way you don't have to go back to coding everytime something changes or a new user comes on board.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question