Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

setting up select query with use of case statements

Posted on 2013-01-22
4
Medium Priority
?
207 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 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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