473,573 Members | 2,870 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query optimization - where - ISNULL

1 New Member
I have a sequence of 20 stored procedures executed to produce the final output. In almost all procedures, i have a table lookup. The query is something similar to shown below:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tableA 
  3. WHERE
  4. ISNULL(col1, @col1) = @col1 AND
  5. ISNULL(col2, @col2) = @col2 AND
  6. ISNULL(col3, @col3) = @col3 AND
  7. ISNULL(col4, @col4) = @col4 AND
  8. @col5 BETWEEN ISNULL(col5, @col5) AND ISNULL(col5, @col5) AND 
  9. @coldate BETWEEN ISNULL(effDate, @coldate) AND ISNULL(expDate, @coldate)
  10. ORDER BY colrk DESC
  11.  
The idea here is, tables have a default value will null in the column. if the given values does not match, it has to return the null valued record.

Is this query optimized? Does using ISNULL() function affects the performance.

We have non clustered index on the table, including all columns in where clause.
Jan 13 '11 #1
1 2390
ck9663
2,878 Recognized Expert Specialist
For the most part, using function in your where CLAUSE will affect the performance. You should also watch out the BETWEEN operator, make sure the first expression is lower than the second expression being compared to. If you don't need to order the result, remove the ORDER BY. And only return the needed column, not all (*) of them.

Good Luck!!!

~~ CK
Jan 13 '11 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
2378
by: Bacci | last post by:
I have two tables. The first is "Locations" which has 52,000 zip codes w/cooresponding latitudes and longitudes. The second "Suppliers" has 2,000 company names and addresses. The user enters a zip code and the cooresponding latitude and longitude is return. $sql = "SELECT l.latitude, l.longitude "; $sql .= "FROM Locations l"; $sql .=...
2
1695
by: ensnare | last post by:
This query is running REAL slow ... like 1.2 secs ... any ideas on how I could optimize it? Perhaps my indexes are incorrect? $this->query = "SELECT m.username as username, e.title as title, e.exhibition_id as exhibition_id, LEFT(e.text,50) as text, e.random_key,
3
2180
by: aroy | last post by:
Hi, Need help in optimizing a query in SQL Server. Following is the problem statement. There are two tables; 1st table (t1) has a KEY ( char(8) ) column, with a clustered index. this is not the primary key. The table can have billions of records; in test environment, we are having 3,000,000 records
5
4402
by: AC Slater | last post by:
Whats the simplest way to change a single stored procedures query optimization level? In UDB8 that is. /F
2
5123
by: Eugene | last post by:
I am trying to set query optimization class in a simple SQL UDF like this: CREATE FUNCTION udftest ( in_item_id INT ) SPECIFIC udftest MODIFIES SQL DATA RETURNS TABLE( location_id INT, period_id INT ) BEGIN ATOMIC SET CURRENT QUERY OPTIMIZATION 1;
12
6166
by: WantedToBeDBA | last post by:
Hi all, db2 => create table emp(empno int not null primary key, \ db2 (cont.) => sex char(1) not null constraint s_check check \ db2 (cont.) => (sex in ('m','f')) \ db2 (cont.) => not enforced \ db2 (cont.) => enable query optimization) DB20000I The SQL command completed successfully. db2 => insert into emp values(1,'m')
3
1572
by: serge | last post by:
How do I determine which method I should use if I want to optimize the performance of a database. I took Northwind's database to run my example. My query is I want to retrieve the Employees' First and Last Names that sold between $100,000 and $200,000. First let me create a function that takes the EmployeeID
1
1349
by: Ed Murphy | last post by:
Query #1: select <list of fields> from C join B on C.b_key = B.b_key join A on B.a_key = A.a_key where A.o_key = <some value> Query #2:
1
3032
by: Don Li | last post by:
Hi, Env: MS SQL Server 2000 DB Info (sorry no DDL nor sample data): tblA has 147249 rows -- clustered index on pk (one key of datatype(int)) and has two clumns, both are being used in joins; intersecTbl4AB has 207016 rows -- clustered index on two fks and
1
1781
by: madankarmukta | last post by:
Hi, One I get the query plan for a particular query.. Can anybody provide me the guidelines for how to proceed for optimization ? Thanks!
0
7755
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8190
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8048
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6385
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5281
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3722
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2183
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1284
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1027
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.