Performance Issue Using Conditional WHERE Clause

Jan 25, 2008

Consider the following two functionally identical example queries:

Query 1:

DECLARE @Name VARCHAR(32)
SET @Name = 'Bob'
SELECT * FROM Employees
WHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name END

Query 2:

SELECT * FROM Employees WHERE [Name] = 'Bob'

I would expect SQL Server to construct an identical QEP under the hood
for these two queries, and that they would require essentially the
same amount of time to execute. However, Query 1 takes much longer to
run on my indexed table of ~300,000 rows. By "longer", I mean that
Query 1 takes about two seconds, while Query 2 returns almost
instantly.

Is there a way to implement a conditional WHERE clause without
suffering this performance hit? I want to avoid using the IF...THEN
method because I frequently require several optional parameters in the
WHERE clause.

Thanks!
Jared

View 6 Replies


ADVERTISEMENT

Conditional WHERE Clause

May 8, 2006

Hi,
[SQL 2005 Express]
I would like a DropDownList to be populated differently depending on the selected value in a FormView.
If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company.  Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.
Here is the SQL for case 1:
SELECT    AdviserID,    AdviserName FROM    Advisers WHERE    (CompanyID = @CompanyID).
Here's the SQL for case 2:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    (CompanyID = @CompanyID) AND    (TypeID = 3).
Here's my best (failed) attempt to get what I want:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    IF @CompanyID = 2 THEN      BEGIN         (CompanyID = @CompanyID)      END   ELSE      BEGIN         (CompanyID = @CompanyID) AND          (TypeID = 3)      END
I've also tried:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE @CompanyID       WHEN 2 THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND          (TypeID = 3)   END
and 
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND (TypeID = 3)   END
I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.
Thanks very much.
Regards
Gary

View 7 Replies View Related

Conditional Where Clause

Dec 14, 2007

Hi all,

I have a table QT defined as

CREATE TABLE [dbo].[QT](
[Query] [nvarchar](50) NULL,
[Frequency] [int] NULL
) ON [PRIMARY]

Now based on a parameter I want to include a predicate in the select statement.

Basically I am trying to write something similar to the one below but possible only usinf one select statement.

if @queryString is null then

select query ,sum(frequency)

from qt

group by query
else
select query ,sum(frequency)

from qt

group by query
where query = @queryString.

Now is there a way to achieve this thing without using two separate select? The actual code I am trying to write is much bigger and I am trying to see if there is more compact way of expressing things.

Thanks
Aye.

View 7 Replies View Related

Conditional Where Clause Possible?

Jun 11, 2007

Is it possible to use a conditional statements in a where clause?



IE: I have 3 paramaters that may or may not be filled.



I would like to do something along the lines of...





Select * From (tables)

WHERE

If @param1 has value

Begin

'run this where statement

if @Param2 has value

'add this to the where clause

if @param3 has value

'add this to the where cluase



View 10 Replies View Related

Conditional If In Where Clause

Oct 8, 2007



Can I use "CASE WHEN ... THEN ... ELSE ... END" in the where clause of a SQL statement? I have sucessfully used it in the select portion of my statment but I would also like to use conditional criteria in the WHERE portion. Any advice is greatly appreciated.

View 7 Replies View Related

How Can I Use A Conditional Where Clause In Sql Statment

May 15, 2007

I have a store procedure where i need to use conditionel where clause

View 2 Replies View Related

Using Conditional Statement In Where Clause

Mar 26, 2012

I'm trying to use a conditional statement in the where clause.

Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000

What I'm trying to achieve:

If Amount = Amount2 for UID 2 then show UID 1

View 4 Replies View Related

Conditional Where Clause W/ Case Statement Possible?

Sep 25, 2007

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.


SELECT uniqueID, lob, xdate
FROM mytable
WHERE

CASE WHEN @myparam = 'ALL'

THEN

xdate >= '2007-09-01'
ELSE

xdate >= '2007-09-01' or
lob = @myparm
END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

View 8 Replies View Related

Transact SQL :: How To Get Results Based On Conditional Where Clause

Jul 14, 2015

My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum              PolicyStartdate
123G                       01/01/2012    
456D                       02/16/2012     
789A                       01/21/2012
163J                       05/25/2012

Now my output should return based on 3 parameters..

First two parameters are date range... let say @fromdt and @todt

Third parameter is @policynum

Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output

Scenario-2: enter policy num in policynum param and don't select any dates
Ex:  policynum ='456D'     It returns 2nd row in the output

Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'.  it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)

I need t-sql code to get above results.

View 12 Replies View Related

Conditional Where Clause Depending On Input Parameter

May 5, 2008

I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.

My simple QueryA

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL

My simple QueryB

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL

I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.

Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?

I know it can be done using dynamic SQL construction. But any other ways ?

Also can someone also give in the solution in PL/SQL.

Thanks a bunch.

Jaffery.

View 7 Replies View Related

Conditional Where Clause With Comma Delimited String And Link Table

Jul 24, 2007

 I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports  (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere   if @sports not null    user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!

View 10 Replies View Related

Help With Performance On A Conditional Trigger

Feb 27, 2004

I have a pretty massive conditional trigger. If there is another way of going about this, please let me know. But I'm populating a temp table with records and based on many conditions, I am transforming this data to another table in a corrected format. These conditions I am using reference the final table in many ways, and this seems to become slower and slower as the final table grows larger.

Take a look and see if you can help me please.

View 2 Replies View Related

T-SQL (SS2K8) :: Performance Of OR In Where Clause

Oct 17, 2014

I had a pretty simple query like

select * from table_A
where email in (select email from table_B)
or
email not in (select email from table_c)

It ran for a very long time.

But if I ran the select with either of the conditions by itself it took just a second. Combining both conditions really slowed it down.

View 4 Replies View Related

Group By Clause Killing Performance

Jun 15, 2007

I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.
Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.
Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.
If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?
Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?
I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.

View 6 Replies View Related

Joins Vs Where Clause - Performance Query

Dec 5, 2006

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ????

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )


USING A WHERE CLAUSE and Alias
-------------------------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
-----------------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

-------------------------------------
It pays to be honest to your DBA

View 4 Replies View Related

Query Performance With Order By Clause?

Jul 20, 2005

Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau

View 1 Replies View Related

SQL Server 2005 Performance - Where Clause Range Retriaval

Sep 28, 2007

Everything is flowing smoothly for the SQL Server Database I have, except one type of retrieval and that is when the where clause has a range of data values to do the retrieval then the performance is terrible. I cannot anticipate every range. There are indexes on the table to try to help; however, nothing seems to help. Has anyone had a similiar problem? Any suggestions to improve performance?

Thanks, Mary

View 2 Replies View Related

Microsoft OLEDB Provider For DB2 Performance Issue (Missing Where Clause On DB2 Site)

Aug 17, 2007

Hi!
I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A
WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the €œwhere clause€?:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on €¦ !

If anyone out there has an idea of what could be wrong, please let me know!


NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

View 5 Replies View Related

Conditional Subscription / Conditional Execution Of Report

Mar 7, 2008



Hello everyone,

Is there a way in order to execute a subscribed report based on a certain criteria?

For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.

My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.


Any help or suggestions will be much appreciated.

Thanks,
Larry

View 6 Replies View Related

Conditional Formatting - Not So Conditional??

Dec 15, 2006

I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.

=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")

I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.

Is it because this is a matrix report? What am I doing wrong?

Thanks in advance . . .

View 4 Replies View Related

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Filtering Results In The Where Clause Vs A Having Clause

Oct 25, 2007

I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(


SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND


TrackID + 'x1' IN


(


SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?


UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(



SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN


(


SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View 3 Replies View Related

[Performance Discussion] To Schedule A Time For Mssql Command, Which Way Would Be Faster And Get A Better Performance?

Sep 12, 2004

1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection

above, which way would be faster and get a better performance?

View 2 Replies View Related

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

2 examples:

1) Rows ordered using textual id rather than numeric id


Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id






Result set is ordered as: 1, 11, 2
I expect: 1,2,11


if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.




Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id




Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


and


Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View 12 Replies View Related

Extremely Poor Query Performance - Identical DBs Different Performance

Jun 23, 2006

Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***

View 2 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

Having Clause Without GROUP BY Clause?

Nov 20, 2004

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View 3 Replies View Related

Top Clause With GROUP BY Clause

Apr 3, 2008

How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent


thanks in advance

regards
Waqas

View 10 Replies View Related

Diff In On Clause And Where Clause?????

Apr 4, 2007

hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)


select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

View 6 Replies View Related

SQL Inner Join Clause And The Where Clause

Jan 21, 2008

Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

Thanks

View 6 Replies View Related

Very Poor Performance - Identical DBs But Different Performance

Jun 22, 2006

Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved