Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

setting up select query with use of case statements

Posted on 2013-01-22
4
Medium Priority
?
214 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 1000 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 1000 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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

916 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