Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

setting up select query with use of case statements

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
armanbena
Asked:
armanbena
  • 2
2 Solutions
 
armanbenaAuthor Commented:
Sorry, here is file.
0
 
armanbenaAuthor Commented:
ATTACHMENT
HELP.txt
0
 
David ToddSenior DBACommented:
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
 
gdemariaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now