Solved

setting up select query with use of case statements

Posted on 2013-01-22
4
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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