Optimizing store proc

I have the following store proc and was wondering if I can optimized it by using a SELECT CASE instead of all those IF? I tried but don't know how to write it.

Thanks

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[Get_Cl_SearchMultiColumn]( @.strSearchTermColumnNamenvarchar (50),@.strSearchTermSearchTermnvarchar (200) )as if (@.strSearchTermColumnName ='Monitor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2FROM Cl_SystemsWHERE contains(Monitor1,@.strSearchTerm)orcontains(Monitor2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='MonitorSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2SerialFROM Cl_SystemsWHERE contains(Monitor1Serial,@.strSearchTerm)orcontains(Monitor2Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Microscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Microscope1,Microscope2FROM Cl_SystemsWHERE contains(Microscope1,@.strSearchTerm)orcontains(Microscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialMicroscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialMicroscope1,SerialMicroscope2FROM Cl_SystemsWHERE contains(SerialMicroscope1,@.strSearchTerm)orcontains(SerialMicroscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Controller')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Controller1,Controller2FROM Cl_SystemsWHERE contains(Controller1,@.strSearchTerm)orcontains(Controller2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ControllerFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Cont1Firmware,Cont2FirmwareFROM Cl_SystemsWHERE contains(Cont1Firmware,@.strSearchTerm)orcontains(Cont2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialController')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialController1,SerialController2FROM Cl_SystemsWHERE contains(SerialController1,@.strSearchTerm)orcontains(SerialController2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Joystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joystick1,Joystick2FROM Cl_SystemsWHERE contains(Joystick1,@.strSearchTerm)orcontains(Joystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='JoystickFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joy1Firmware,Joy2FirmwareFROM Cl_SystemsWHERE contains(Joy1Firmware,@.strSearchTerm)orcontains(Joy2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialJoystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialJoystick1,SerialJoystick2FROM Cl_SystemsWHERE contains(SerialJoystick1,@.strSearchTerm)orcontains(SerialJoystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Camera')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4FROM Cl_SystemsWHERE contains(Camera1,@.strSearchTerm)orcontains(Camera2,@.strSearchTerm)orcontains(Camera3,@.strSearchTerm)orcontains(Camera4,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='CameraSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1Serial,Camera2Serial,Camera3Serial,Camera4SerialFROM Cl_SystemsWHERE contains(Camera1Serial,@.strSearchTerm)orcontains(Camera2Serial,@.strSearchTerm)orcontains(Camera3Serial,@.strSearchTerm)orcontains(Camera4Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ZMotor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3FROM Cl_SystemsWHERE contains(ZMotor1,@.strSearchTerm)orcontains(ZMotor2,@.strSearchTerm)orcontains(ZMotor3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Stage')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3FROM Cl_SystemsWHERE contains(Stage1,@.strSearchTerm)orcontains(Stage2,@.strSearchTerm)orcontains(Stage3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Lens')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Lens1,Lens2,Lens3FROM Cl_SystemsWHERE contains(Lens1,@.strSearchTerm)orcontains(Lens2,@.strSearchTerm)orcontains(Lens3,@.strSearchTerm)return 0end

I don't know why you need a WHERE clause with CONTAINS predicate but try the link below for CASE statement for an expert. Hope this helps.

http://www.craigsmullins.com/ssu_0899.htm

|||

Well it seems that I am trying to use the CASE in a different situation then what explain everywhere.

I would like to use it one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTermColumnName

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

Oups I made a few mistakes in my last statement, I should have been:

Well it seems that I am trying to use the CASE in a different situation than what explained everywhere.

I would like to use it on one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTerm

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

What I am saying is I don't know if you can use the CONTAINS predicate with CASE statement. The links below one is a full text expert he knows more about fulltext and the other two are the two versions of CONTAINS. Hope this helps.

http://spaces.msn.com/jtkane/

http://msdn2.microsoft.com/en-US/library/ms189760.aspx

http://msdn2.microsoft.com/en-US/library/ms187787.aspx

sql

Optimizing SELECT query

Hi

I have one table (tableDemo) with following structure:

TSID bigint (Primary Key)
TskID bigint
Sequence bigint
Version bigint
Frequency varchar(500)
WOID bigint
DateSchedule datetime
TimeStandard real
MeterEstimated real
MeterLast real
Description ntext
CreatedBy bigint
CreatedDate datetime
ModifiedBy bigint
ModifiedDate datetime
Id uniqueidentifier

I have 8000 records in this table. When I fire simple select command (i.e. select * from tableDemo) it takes more than 120 seconds.

Is there any techniques where I can access all these records within 2-3 seconds ?

Regards,

ND

Why do you need every field of every record? Surely you are not going to display them all on one page? If you want to use them for paging, and are using Sql Server 2005, have a look at ROW_NUMBER:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

|||

Are there any large files stored in any of the rows being returned? i.e. files or larges amounts of text? Returning only 8000 rows shouldnt take very long esp not 120 seconds. Is the instance of sql on your local machine or located someone else?

Tim

Optimizing Reports

I've got a report thats somewhat time consuming that runs on my
reporting server, and what I've found is that if I select anything more
than a few months in my daterange parameters, its like 5 minutes to run.
What I'd like to know...
Can I snapshot a years worth of default data, and have the report run
off the snapshot, but allow you to specify date range within the two dates?
Thanks in advance
WestonWeston Weems wrote:
> I've got a report thats somewhat time consuming that runs on my
> reporting server, and what I've found is that if I select anything more
> than a few months in my daterange parameters, its like 5 minutes to run.
> What I'd like to know...
> Can I snapshot a years worth of default data, and have the report run
> off the snapshot, but allow you to specify date range within the two dates?
> Thanks in advance
> Weston
I think a linked report with the default parameter of a years worth of
data setup in a snapshot would be good. Then just reference the linked
report and change the parameters. The report should be served from the
snapshot.
Just theory, I haven't tried it to see if it would work.

Optimizing query execution...

We have SQL Server 2000 and int is an Oracle linked server. I'm trying to run the following query...

SELECT DISTINCT a.auf_nr AS OrderNo,
e.ku_name AS Customer,
d.bestell_dat AS OrdDate,
d.liefer_dat AS DelvDate,
CAST(SUM(b.anz) AS FLOAT) Qty,
CAST(SUM((CAST(c.breite AS FLOAT) / 1000 * CAST(c.hoehe AS FLOAT) / 1000) * b.anz) AS FLOAT) SQM,
CAST(SUM(a.liefer_offen) - (SUM(a.anz) - SUM(b.anz)) AS FLOAT) AvailDelv,
CAST(SUM(a.liefer_anz) AS FLOAT) Delvd,
CAST(SUM(c.sum_brutto*a.anz) AS FLOAT) Value

FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_LIP_STATUS b,
liorder..LIORDER.AUF_POS c,
liorder..LIORDER.AUF_KOPF d,
liorder..LIORDER.KUST_ADR e

WHERE a.auf_nr = b.auf_nr and
b.auf_nr = c.auf_nr and
c.auf_nr = d.auf_nr and
d.kunr = e.ku_nr and
a.auf_pos = b.auf_pos and
b.auf_pos = c.auf_pos and
b.lip_status = 7 and
c.ver_art !='V' and
a.history = 0 and
a.rg_stat != 2 and
e.ku_name IS not null and
e.ku_vk_ek = 0 and
d.bestell_dat BETWEEN '01/01/2005' and '12/17/2005'

GROUP BY a.auf_nr,
d.liefer_dat,
b.lip_status,
d.bestell_dat,
e.ku_name,
d.kopf_tour,
d.kopf_firma

HAVING CAST(SUM(a.liefer_offen)-(SUM(a.anz)-SUM(b.anz)) AS FLOAT) > 0

..and it takes around 2 minutes to show the results even if the date range is of the same date. I even tried to use an indexed column but I still get the same slow execution time. I even tried to create a UDF so that the WHERE clause would be resolved remotely on the Oracle DB but still the same. Is there anyway I can do it in much more efficient and faster way?I'd use OPENQUERY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp).

-PatP

Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

sql

Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

Optimizing an IN clause

I have a fairly straightforward SELECT query that includes the following:
MembersTable.MemberID IN
(
SELECT ZipcodesTable.MemberID
FROM ZipcodesTable.Zipcode IN
(
'01234','03631','55902' ... '03036'
)
That is, it's looking for entries in the ZipcodeTable where the Zipcode
value is any one of a very large set of zipcodes, up to 500 Zipcodes. My
Zipcode field is a 5 character field.
Would the query run faster if I made the Zipcode field an int instead of 5
chars?
Are there other ways to speed up the zipcode-matching part of my query?
- Roger GarrettChanging to integer is not possible in this case, cause you got leading
zero in your zip code. Converting them to INT would trim them away. The
best way to speed this up would be top store the ZIP codes in a table
to join them. It would be even more easy to manage.
HTH, Jens Suessmeyer.|||Relations. The performance of this query would benefit from using a join
instead of using the IN operator.
How do the values get into the query?
ML
http://milambda.blogspot.com/|||Have you tried placing the zipcodes in a table and using EXISTS instead of
IN?
Andrew J. Kelly SQL MVP
"Roger Garrett" <RogerGarrett@.discussions.microsoft.com> wrote in message
news:5C55A707-6E72-4CDF-BFC0-7320AA8CDA12@.microsoft.com...
>I have a fairly straightforward SELECT query that includes the following:
> MembersTable.MemberID IN
> (
> SELECT ZipcodesTable.MemberID
> FROM ZipcodesTable.Zipcode IN
> (
> '01234','03631','55902' ... '03036'
> )
> That is, it's looking for entries in the ZipcodeTable where the Zipcode
> value is any one of a very large set of zipcodes, up to 500 Zipcodes. My
> Zipcode field is a 5 character field.
> Would the query run faster if I made the Zipcode field an int instead of 5
> chars?
> Are there other ways to speed up the zipcode-matching part of my query?
> - Roger Garrett
>|||>> Would the query run faster if I made the Zipcode field [sic] an INTEGER instea
d of CHAR(5)? <<
ZIP codes are CHAR(5) and not INTEGER. Columns are not fields.
For a large number of zip codes, you might find using a table instead
of a list is faster. It would have an index on its single column.
SELECT member_id
FROM Membership
WHERE zip_code
IN (SELECT zip_code FROM ZipLists);|||Hi Roger,
Make sure you have an index ZipCode, MemberID on the ZipcodesTable.
Also, use EXISTS instead of IN.
AND EXISTS (
SELECT *
FROM ZipcodesTable zt
WHERE zt.Zipcode IN ( ...... )
AND zt.MemberID = MembersTable.MemberID
)
Even better if you could put the IN clause into a table of its own...
AND EXISTS (
SELECT *
FROM #ZCodes zt
WHERE zt.MemberID = MembersTable.MemberID
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Roger Garrett" <RogerGarrett@.discussions.microsoft.com> wrote in message
news:5C55A707-6E72-4CDF-BFC0-7320AA8CDA12@.microsoft.com...
>I have a fairly straightforward SELECT query that includes the following:
> MembersTable.MemberID IN
> (
> SELECT ZipcodesTable.MemberID
> FROM ZipcodesTable.Zipcode IN
> (
> '01234','03631','55902' ... '03036'
> )
> That is, it's looking for entries in the ZipcodeTable where the Zipcode
> value is any one of a very large set of zipcodes, up to 500 Zipcodes. My
> Zipcode field is a 5 character field.
> Would the query run faster if I made the Zipcode field an int instead of 5
> chars?
> Are there other ways to speed up the zipcode-matching part of my query?
> - Roger Garrett
>|||Jens,
I don't see that leading zeros would cause any problem. I'm suggesting that
I change the Zipcodes column from char 5 to int and to just store the
numerical integer values of the zipcodes rather than the 5-character string.
A zipcode of "03036" would become a 3036 in the column, and when I'm
searching for an "03036" it would match with the 3036 value. Of course, I
would specify numeric values (e.g. 03036) rathet than quoted strings
('03036').
I'm assuming that a 5-char column occupies at least 6 bytes (to make it on
an even byte boundary) and that an int occupies 4 bytes. That at a minium
saves some space in the database. And then, when I'm looking for a particula
r
value, it only has to compare 4 bytes (which is mostly likely a single
hardware instruction) instead of six bytes, so the queries should run a bit
faster.|||ML,
My application program constructs the query.
What's happenning here is this: I have a Zipcodes table. That table has two
columns, a MemberID column and a Zipcode column. Each member has one entry i
n
this table, signifying the zipcode of where he lives.
At certain times during the running of my application it needs to know the
MemebrIDs of all the members that live within a certain radius of a given
member. My application program figures out which zipcodes are within that
radius and constructs an array of strings signifying that set of zipcodes. I
t
then constructs a query, using that array of strings, in order to get from
the database the set of MemberIDs, from the Zipcodes table, of those members
who reside in any of those zipcodes. The query looks something like:
SELECT ZipcodesTable.MemberID
FROM ZipcodesTable.Zipcode IN
(
'01234','03631','55902' ... lots of zipcodes here ... '03036'
)
As far as I can tell that means that SQL Server has to compare each and
every zipocde in the Zipcodes table with (possibly all of) the zipcodes
within the IN clause of the query. In fact, for MOST of the rows in the
Zipcodes table it will have to do the comparison against ALL of the zipcodes
in the IN clause, since most of the members will not be within ANY of those
zipcodes.
Now, if SQL Server were smart it might order those zipcodes from the IN
clause and determine the smallest and largest zipcode values and thereby do
a
much quicker comparison at each row. But I don't know that I can rely on SQL
Server being that smart.
So I'm looking for a better way to express the query so that it runs as fast
as possible.|||Andrew,
I wasn't familiar with the EXISTS operator (I'm very new to all this) so I
just now read up on it. I don't see how EXISTS will help. How are you
suggesting that the zipcodes be put in a table? Do you mean the set of
zipcodes that I'm looking for for the specific current query? WHat would the
EXISTS query look like?
Please see my reply to Jens for a (hopefully) clearer description of what
I'm trying to accomplish.
"Andrew J. Kelly" wrote:

> Have you tried placing the zipcodes in a table and using EXISTS instead of
> IN?
> --
> Andrew J. Kelly SQL MVP
>
> "Roger Garrett" <RogerGarrett@.discussions.microsoft.com> wrote in message
> news:5C55A707-6E72-4CDF-BFC0-7320AA8CDA12@.microsoft.com...
>
>|||For the performace reason: Try it out, once you converted this into
have your data as a char. (which is like I said and meanwhile also
Steve pointed out, preferable, because you don=B4t have to deal with
later problems around this. We has ourselves in Germany a change from
4digit numbers to 5 digits with a trailing zero. I can tell you, that
was for many software vendors like the Y2k problem).
These is my opinion, my personal experience and advice for you.
HTH, Jens Suessmeyer.

Optimizing a query

Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi
I have a query which I would like to optimize and thought I had found a
solution.
The query is a join of several selects from several different tables.
So we have in general the following:
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
or something like that. Of course my query was a lot larger than this,
but it is just to illustrate the problem.
The above was too slow, and I made an attempt to optimize it and
thought it did work. The following is what I did:
Instead of the SELECT's I could create temporary tables and select into
those so I get the following:
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
And then join those two tables:
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
and at the end drop the temporary tables.
DROP Table #temp_table1
DROP Table #temp_table2
This gives the same result as the original statement.
I profiled a bit and saw that if I created the temp tables, and ran the
join statement several times after each other, the first time it is run
it take a lot of time compared to the following. Can anyone explain to
me why this is the case? I thought that the temporary tables were
created and loaded into the cache, and therefore all calls to the big
JOIN statement should take equally long time.
The thing is that I cannot reuse the temporary tables, so I get no
benefit from all the above.
Thank you very much in advance
Jeeji|||Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:

Optimizer do not use right INDEX

Hi,
I'm sending this again because i'm very confused :
This Query :
SELECT 0
FROM
WOTransit -- with (index=x1,readuncommitted)
INNER JOIN WO On WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
Since this morning this query is much more slow than usual
If i add : "with (index=x1,readuncommitted) ", the speed
is back to normal. because optimiser normaly use that
index.
If not, optimizer is using the clustered index X2 (with
full scan ...:o( ) that is not related to the query.
why doesn't he use the right index ?
What can i do to fix that ? i allready :
- ran UPDATEUSAGE and no change.
- droped created index X1. no change
- reran UPDATEUSAGE and no change.
- Updatstats has been ran earlyer this morning
I don't want to drop create X2 now because i'm in
production and i will block every one for at least an hour.
Please, some help
Thank you !
Donald
Pls see my response to your other post.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1c43c01c45239$c4001610$a401280a@.phx.gbl...
> Hi,
> I'm sending this again because i'm very confused :
> This Query :
> SELECT 0
> FROM
> WOTransit -- with (index=x1,readuncommitted)
> INNER JOIN WO On WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
>
> Since this morning this query is much more slow than usual
> If i add : "with (index=x1,readuncommitted) ", the speed
> is back to normal. because optimiser normaly use that
> index.
> If not, optimizer is using the clustered index X2 (with
> full scan ...:o( ) that is not related to the query.
> why doesn't he use the right index ?
> What can i do to fix that ? i allready :
> - ran UPDATEUSAGE and no change.
> - droped created index X1. no change
> - reran UPDATEUSAGE and no change.
> - Updatstats has been ran earlyer this morning
> I don't want to drop create X2 now because i'm in
> production and i will block every one for at least an hour.
> Please, some help
> Thank you !
> Donald
>

Optimizer do not use right INDEX

Hi,
I'm sending this again because i'm very confused :
This Query :
SELECT 0
FROM
WOTransit -- with (index=x1,readuncommitted)
INNER JOIN WO On WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
Since this morning this query is much more slow than usual
If i add : "with (index=x1,readuncommitted) ", the speed
is back to normal. because optimiser normaly use that
index.
If not, optimizer is using the clustered index X2 (with
full scan ...:o( ) that is not related to the query.
why doesn't he use the right index ?
What can i do to fix that ? i allready :
- ran UPDATEUSAGE and no change.
- droped created index X1. no change
- reran UPDATEUSAGE and no change.
- Updatstats has been ran earlyer this morning
I don't want to drop create X2 now because i'm in
production and i will block every one for at least an hour.
Please, some help
Thank you !
DonaldPls see my response to your other post.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1c43c01c45239$c4001610$a401280a@.phx.gbl...
> Hi,
> I'm sending this again because i'm very confused :
> This Query :
> SELECT 0
> FROM
> WOTransit -- with (index=x1,readuncommitted)
> INNER JOIN WO On WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
>
> Since this morning this query is much more slow than usual
> If i add : "with (index=x1,readuncommitted) ", the speed
> is back to normal. because optimiser normaly use that
> index.
> If not, optimizer is using the clustered index X2 (with
> full scan ...:o( ) that is not related to the query.
> why doesn't he use the right index ?
> What can i do to fix that ? i allready :
> - ran UPDATEUSAGE and no change.
> - droped created index X1. no change
> - reran UPDATEUSAGE and no change.
> - Updatstats has been ran earlyer this morning
> I don't want to drop create X2 now because i'm in
> production and i will block every one for at least an hour.
> Please, some help
> Thank you !
> Donald
>

Optimizer do not use right INDEX

Hi,
I'm sending this again because i'm very confused :
This Query :
SELECT 0
FROM
WOTransit -- with (index=x1,readuncommitted)
INNER JOIN WO On WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
Since this morning this query is much more slow than usual
If i add : "with (index=x1,readuncommitted) ", the speed
is back to normal. because optimiser normaly use that
index.
If not, optimizer is using the clustered index X2 (with
full scan ...:o( ) that is not related to the query.
why doesn't he use the right index ?
What can i do to fix that ? i allready :
- ran UPDATEUSAGE and no change.
- droped created index X1. no change
- reran UPDATEUSAGE and no change.
- Updatstats has been ran earlyer this morning
I don't want to drop create X2 now because i'm in
production and i will block every one for at least an hour.
Please, some help
Thank you !
DonaldPls see my response to your other post.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1c43c01c45239$c4001610$a401280a@.phx
.gbl...
> Hi,
> I'm sending this again because i'm very confused :
> This Query :
> SELECT 0
> FROM
> WOTransit -- with (index=x1,readuncommitted)
> INNER JOIN WO On WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
>
> Since this morning this query is much more slow than usual
> If i add : "with (index=x1,readuncommitted) ", the speed
> is back to normal. because optimiser normaly use that
> index.
> If not, optimizer is using the clustered index X2 (with
> full scan ...:o( ) that is not related to the query.
> why doesn't he use the right index ?
> What can i do to fix that ? i allready :
> - ran UPDATEUSAGE and no change.
> - droped created index X1. no change
> - reran UPDATEUSAGE and no change.
> - Updatstats has been ran earlyer this morning
> I don't want to drop create X2 now because i'm in
> production and i will block every one for at least an hour.
> Please, some help
> Thank you !
> Donald
>

Optimize speed of Hosted SQL Server

Hi,

I'm retrieving data in VBA using simple SELECT statements on my Hosted SQL Server. How do I most effectively speed up the process?

Is it just about data amount, traffic, and speed of Internet conncetion?

Or should I use Stored Procedures or should I index tables?

Is there a function to kind of "flush" the data to the VBA program as it is all READ-ONLY?

Any help is much appreciated,

Jakob

Can you give more information about how your database schema looks like, what type of queries you are doing, and how your network architecture looks like?

Based on that it might be easier to answer your questions.

Thanks,

Marcel van der Holst
[MSFT]|||

The queries are very simple, just simple "SELECT xx, xx, xx FROM xx WHERE xx=xx"

I'm connecting from home with my lapto to a hosted SQL Server, so no big network. I have tried testing my code to see how and when it is slow. It it quite fast to open the connection, but when I try to through the records using for example the rs.movenext function I can see that it takes a second or two each time for each record and each field in each record. It seems like I'm maintaining an open connection over the internet when I actually just want to the data read-only.

Does that help?

Jakob

|||Would it be possible to run the queries against a local SQL Server that runs on your laptop. That way, you can ignore the network latency, and see if the query is causing the problem.

If you use RecordSet, it will keep the connection open until you explicitely close it. Even when you close it, connection pooling will be used to keep the connection open a while, just in case you need it again.

How much data do you select, i.e. how big is the data in the select xx,xx statement? if that data is big, and you have to go over a big network, it might cause the slowdown..

Another thing to consider is to retrieve all the data you need in a big select statement (select * from Table), and then store this data in a local cache, and do the searching and filtering locally, instead of doing it over the internet.

Thanks,

Marcel van der Holst
[MSFT]|||

the database is about 30mb. The queries are quite simple - just "SELECT xx, xx FROM xx WHERE xx=yy".

I just converted my Access 2003 database, which was stored locally, to hosted SQL Server. An update which previously took about 10 seconds now takes about 2 minutes.

I tried to limit the number of rows which helped quite a lot. Then I also tried to change the format of a field from "nText" to "nVar" that also helped a lot. It seems like the amount of data transmitted is the single most important factor - even though I'm running on a 8mbit line.

Most of my data I just need in arrays - I usually don't update any tables. My thought was that it should be possible to send the query result from the SQL Server in some kind of "flat" text array format and not in a recordset format.

I'm using ADODB in VBA with code lines like:

Set rsData = New ADODB.Recordset

rsData.Open sSql, conGPAM, adOpenKeyset, adLockOptimistic

aData = rsData.GetRows(.1)

I have tried to change LockType and CursorType, but that doesn't really make a difference.

Any suggestions?
|||

ADO is horrible for direct access. Use sprocs. Use output parameters if guaranteed to return just one row or if you only need a single output (like a count).

Check the network latency to your host.

Appropriate indexing is paramount for optimal perfomance. This is somewhat science and somewhat art/experience.

Optimize speed of Hosted SQL Server

Hi,

I'm retrieving data in VBA using simple SELECT statements on my Hosted SQL Server. How do I most effectively speed up the process?

Is it just about data amount, traffic, and speed of Internet conncetion?

Or should I use Stored Procedures or should I index tables?

Is there a function to kind of "flush" the data to the VBA program as it is all READ-ONLY?

Any help is much appreciated,

Jakob

Can you give more information about how your database schema looks like, what type of queries you are doing, and how your network architecture looks like?

Based on that it might be easier to answer your questions.

Thanks,

Marcel van der Holst
[MSFT]|||

The queries are very simple, just simple "SELECT xx, xx, xx FROM xx WHERE xx=xx"

I'm connecting from home with my lapto to a hosted SQL Server, so no big network. I have tried testing my code to see how and when it is slow. It it quite fast to open the connection, but when I try to through the records using for example the rs.movenext function I can see that it takes a second or two each time for each record and each field in each record. It seems like I'm maintaining an open connection over the internet when I actually just want to the data read-only.

Does that help?

Jakob

|||Would it be possible to run the queries against a local SQL Server that runs on your laptop. That way, you can ignore the network latency, and see if the query is causing the problem.

If you use RecordSet, it will keep the connection open until you explicitely close it. Even when you close it, connection pooling will be used to keep the connection open a while, just in case you need it again.

How much data do you select, i.e. how big is the data in the select xx,xx statement? if that data is big, and you have to go over a big network, it might cause the slowdown..

Another thing to consider is to retrieve all the data you need in a big select statement (select * from Table), and then store this data in a local cache, and do the searching and filtering locally, instead of doing it over the internet.

Thanks,

Marcel van der Holst
[MSFT]|||

the database is about 30mb. The queries are quite simple - just "SELECT xx, xx FROM xx WHERE xx=yy".

I just converted my Access 2003 database, which was stored locally, to hosted SQL Server. An update which previously took about 10 seconds now takes about 2 minutes.

I tried to limit the number of rows which helped quite a lot. Then I also tried to change the format of a field from "nText" to "nVar" that also helped a lot. It seems like the amount of data transmitted is the single most important factor - even though I'm running on a 8mbit line.

Most of my data I just need in arrays - I usually don't update any tables. My thought was that it should be possible to send the query result from the SQL Server in some kind of "flat" text array format and not in a recordset format.

I'm using ADODB in VBA with code lines like:

Set rsData = New ADODB.Recordset

rsData.Open sSql, conGPAM, adOpenKeyset, adLockOptimistic

aData = rsData.GetRows(.1)

I have tried to change LockType and CursorType, but that doesn't really make a difference.

Any suggestions?
|||

ADO is horrible for direct access. Use sprocs. Use output parameters if guaranteed to return just one row or if you only need a single output (like a count).

Check the network latency to your host.

Appropriate indexing is paramount for optimal perfomance. This is somewhat science and somewhat art/experience.

Optimize Query.

Hi All,

please look into below query.

SELECT TOP 101 Cert_WarehouseDetails.CertID, Cert_WarehouseDetails.UnderwriterID,
Cert_WarehouseDetails.WarehouseEntriesID, Cert_WarehouseDetails.DivisionWarehouseLocID,
Cert_WarehouseDetails.TypeID, Cert_WarehouseDetails.ReportingType, Cert_WarehouseDetails.CertWHID,
Cert_WarehouseDetails.ClientPolicyDivisionID, Cert_WarehouseDetails.RecordNO, Cert_WarehouseDetails.InsuredValueX,
Cert_WarehouseDetails.PremiumTotalX, Cert_WarehouseDetails.StatusX, Cert_WarehouseDetails.StatusID,
Cert_WarehouseDetails.AuthorizeDateX, Cert_WarehouseDetails.CodeX, Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.BillofLadingDateX,
DOGX, Cert_WarehouseDetails.ConveyanceNameX, Cert_WarehouseDetails.LogonNameX , Cert_WarehouseDetails.ClientNameX,
(CASE WHEN UnderwriterID = 0 THEN ' ' ELSE tblClientUsers.FirstName + ' ' + tblClientUsers.LastName END) AS OwnedBy,
(CASE WHEN UnderwriterID = 0 THEN CAST(' ' AS NVARCHAR) ELSE CAST(TakeOwnershipDate as nvarchar) END) AS OwnedDate

FROM
( SELECT tblCertificates.[CertID] AS CertID, tblCertificates.[UnderwriterID] AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, NULL AS WarehouseEntriesID, NULL AS DivisionWarehouseLocID , tblCertificates.CertID AS CertWHID, tblCertificates.ClientPolicyDivisionsID AS ClientPolicyDivisionID, tblCertificates.CertificateNo AS RecordNO, tblCertificates.TotalInsured AS InsuredValueX, (CASE WHEN tblCertificates.Status=101 or tblCertificates.Status=104 or tblCertificates.DivReferral=1 THEN 0 ELSE PremiumTotal-tblCertificates.Discount END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblCertificates.Status < '105' and tblCertificates.Status <> '103' THEN null ELSE AuthorizeDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblCertificates.IssuedDate AS IssuedDateX, tblCertificates.BillofLadingDate AS BillofLadingDateX, tblCertificates.DescriptionofGoods AS DOGX, tblCertificates.ConveyanceName AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblCertificates.TakeOwnershipDate AS TakeOwnershipDate, tblCertificates.ClientID AS ClientID, tblCertificates.Producer AS BrokerID, tblCertificates.SBU AS SBU, tblCertificates.AssociationID AS AssociationID, tblCertificates.AssuredName AS AssuredName, tblCertificates.UserID AS UserID, tblCertificates.Demoflag AS Demoflag FROM tblCertificates, tblReportingType,tblcurrency,tblClientUsers,tblCli ent,tblStatus WHERE tblCertificates.reportType = tblReportingType.TypeID AND tblCertificates.PremiumCurrencyType = tblCurrency.ID AND tblclientUsers.UserID = tblCertificates.UserID AND tblcertificates.ClientId = tblclient.ClientID AND tblStatus.StatusID = tblCertificates.Status
UNION
SELECT NULL AS CertID, NULL AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, tblWarehouseEntries.[WarehouseEntriesID] AS WarehouseEntriesID, tblWarehouseEntries.[DivisionWarehouseLocID] AS DivisionWarehouseLocID , tblWarehouseEntries.WarehouseEntriesID AS CertWHID, tblWarehouseEntries.ClientPolicyDivisionID AS ClientPolicyDivisionID, tblWarehouseEntries.WarehouseEntriesID AS RecordNO, (CASE WHEN ValueCurrencyType=0 THEN 0 ELSE UnitValue END) AS InsuredValueX, (CASE WHEN tblWarehouseEntries.StatusID=101 THEN 0 ELSE PremiumSum END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblWarehouseEntries.StatusID < '105' THEN null ELSE ApprovalDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblWarehouseEntries.IssuedDate AS IssuedDateX, tblWarehouseEntries.PeriodEndDate AS BillofLadingDateX, LocName AS DOGX, '' AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblWarehouseEntries.TakeOwnershipDate AS TakeOwnershipDate, tblWarehouseEntries.ClientID AS ClientID, tblWarehouseEntries.BrokerID AS BrokerID, tblWarehouseEntries.SBU AS SBU, tblWarehouseEntries.AssociationID AS AssociationID, tblWarehouseEntries.AssuredName AS AssuredName, tblWarehouseEntries.UserID AS UserID, tblWarehouseEntries.Demoflag AS Demoflag FROM tblWarehouseLoc, tblWarehouseEntries, tblReportingType,tblCurrency ,tblClientUsers,tblClient, tblDivisionWarehouseLoc,tblStatus WHERE tblWarehouseEntries.reportTypeID = tblReportingType.TypeID and tblWarehouseLoc.WarehouseLocationID = tblDivisionWarehouseLoc.WarehouseLocID and tblDivisionWarehouseLoc.DivisionWarehouseLocID = tblWarehouseEntries.DivisionWarehouseLocID and tblWarehouseEntries.PremiumCurrencyType = tblCurrency.ID and tblWarehouseEntries.UserID = tblClientUsers.UserID and tblClient.ClientID = tblWarehouseEntries.ClientID AND tblStatus.StatusID = tblWarehouseEntries.StatusID

) AS Cert_WarehouseDetails LEFT JOIN tblClientUsers ON Cert_WarehouseDetails.UnderwriterID = tblClientUsers.UserID

WHERE 1 = 1 AND Cert_WarehouseDetails.Demoflag = 0 and
(convert(datetime,convert(nvarchar,Cert_WarehouseD etails.IssuedDateX,101)) >= '1/1/2003') and
(Cert_WarehouseDetails.IssuedDateX is not null and
convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')
ORDER BY Cert_WarehouseDetails.RecordNO

In above query,as
1. Union (INNER) query returns me 200000 records.

2. If I run above query it takes 18 sec because no index on Cert_WarehouseDetails.RecordNO and i'm ordering on that.

3. When I avoid to 'ORDER BY Cert_WarehouseDetails.RecordNO' then query takes 2 sec.

4. In this case Do I have to use 'nvarchar'
convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')

5. Why do we have to use 'nvarchar' can you explain to me in above statement..

Can you provide me alternate solution so I can make it fast.

Please reply to me asap. Thanks in advance.

Regards,
M. JainTry to create a clustered index on Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.RecordNO.

Also, to avoid conversion, assuming that time portion also exists in this field, just use:

...Cert_WarehouseDetails.IssuedDateX >= '1/1/2003' and Cert_WarehouseDetails.IssuedDateX < '2/1/2003' and Cert_WarehouseDetails.IssuedDateX is not null.|||Originally posted by rdjabarov
Try to create a clustered index on Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.RecordNO.

Also, to avoid conversion, assuming that time portion also exists in this field, just use:

...Cert_WarehouseDetails.IssuedDateX >= '1/1/2003' and Cert_WarehouseDetails.IssuedDateX < '2/1/2003' and Cert_WarehouseDetails.IssuedDateX is not null.

What data type is Cert_WarehouseDetails.IssuedDateX?

Why are you converting it from a datetime to a nvarchar then back to a datetime?|||Originally posted by rdjabarov
Try to create a clustered index on Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.RecordNO.

Also, to avoid conversion, assuming that time portion also exists in this field, just use:

...Cert_WarehouseDetails.IssuedDateX >= '1/1/2003' and Cert_WarehouseDetails.IssuedDateX < '2/1/2003' and Cert_WarehouseDetails.IssuedDateX is not null.

How do I create Index & where because 'Cert_WarehouseDetails' in sot a table.|||What is it? A view/function?|||Originally posted by o_morehart
What data type is Cert_WarehouseDetails.IssuedDateX?

Why are you converting it from a datetime to a nvarchar then back to a datetime?

1. Datetime Datatype.
2. Because I want to compare date with the style of 'mm/dd/yyyy' format.|||Originally posted by rdjabarov
What is it? A view/function?

In view/ function I can't use order by.
I can't create index if query has Union.|||Originally posted by gan_us2003
1. Datetime Datatype.
2. Because I want to compare date with the style of 'mm/dd/yyyy' format. 1. When you compare a datetime field to 'mm/dd/yyyy' the constant gets implicitly converted to datetime data type with the value yyyy-mm-dd 00:00:00.000
2. You do not need to compare "with the style." Style will come later ;)|||Originally posted by rdjabarov
1. When you compare a datetime field to 'mm/dd/yyyy' the constant gets implicitly converted to datetime data type with the value yyyy-mm-dd 00:00:00.000
2. You do not need to compare "with the style." Style will come later ;)

Thanks .
Do you have any Idea where I can create index on RecordNo.|||...On the table where it's coming from...|||Originally posted by rdjabarov
...On the table where it's coming from...

please Can you look at above query.
That is inherited table on that I can't create index.
is anyone know about that.sql

Optimize Query.

Hi,
The below query is taking lots of cpu and IO ...how can we optimize it..
SELECT Id, CorpCode, Name, CorpGroup, GroupId, LocId, StateId, AssocBranchId, WalkIn, Checker,AuthStatus, OpType, RejectReason FROM MCorporate WHERE (AuthStatus = 2 OR (AuthStatus=3 AND OpType != 1 AND OpType != 5)) AND Enabled='Y'
ThanksHi Sanjay
Much more information is needed for someone to be able to tune a query. If
it were simply a matter of looking at a query, and making suggestions, it
would be automated already and nobody would have any performance issues. :-)
At a MINIMUM, we would need:
DDL of the table and its indexes
Output of SET SHOWPLAN_ALL ON
Number of rows in the table, and data distribution for AuthStatus, OpType
and Enabled
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:78614DC8-4295-456C-91AF-6355519D6019@.microsoft.com...
> Hi,
> The below query is taking lots of cpu and IO ...how can we optimize it..
> SELECT Id, CorpCode, Name, CorpGroup, GroupId, LocId, StateId,
AssocBranchId, WalkIn, Checker,AuthStatus, OpType, RejectReason FROM
MCorporate WHERE (AuthStatus = 2 OR (AuthStatus=3 AND OpType != 1 AND OpType
!= 5)) AND Enabled='Y'
> Thanks
>

optimize queries.

how to optimize the following queries..
1) SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
2) select * from tbla where name like '%F'
Regards
sanjaysanjay
It is to hard to say . You did not provide any info about how many data
exists in the table?
How often you query the table?
What is indexes on the table?
My guess.
1) SELECT * FROM employee
WHERE NOT EXISTS
( SELECT jobs .job_id
FROM jobs WHERE employee.job_id
=jobs.job_id AND min_lvl = 25 )

quote:

---

> 2) select * from tbla where name like '%F'

---

This one may prevent query optimyzer for using an index on the name column
unless you re-write it as 'F%'.
Consider to use covering indexes instead of select * from table .
For more details please refer to BOL
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:19EA7547-8EBC-43FF-9981-3AF635B34A03@.microsoft.com...

quote:

---

> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> 2) select * from tbla where name like '%F'
> Regards
> sanjay

---

|||hi
im using not In in my query..which as per documents/sqlserver etc..is not th
e best way to use and it hampers performance...how i can avoide not In..
execution plan of Not In/ Not exists are same..
suppost i ve 1 million records in this tables and its being heavily used..
both are normal tables from pUbs database..
==========
2) if we rewrite the query as 'F%' this will change the logic of the query..
keeping same logic how can we modify the query.|||sanjay
1) Look , another approach but you will have to compare all methods .
1) SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
2)
http://www.sql-server-performance.c...ing_indexes.asp
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFD7096E-56CF-4C1C-B71D-F4862A8EA60D@.microsoft.com...

quote:

---

> hi
> im using not In in my query..which as per documents/sqlserver etc..is not

---

the best way to use and it hampers performance...how i can avoide not In..

quote:

---

> execution plan of Not In/ Not exists are same..
> suppost i ve 1 million records in this tables and its being heavily used..
> both are normal tables from pUbs database..
> ==========
> 2) if we rewrite the query as 'F%' this will change the logic of the

---

query..keeping same logic how can we modify the query.

quote:

---

>

---

|||hi
i tried ..but seems some problem.
SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
the above query returned 34 rows...whereas the below query only 9 rows...i t
hink its using left join bcoz of that..
SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
any further updates would be highly appreciated..
regards|||sanjay
Can post DDL + sample data + expected result and then I can answer you why
it returns 9 rows and why it returns 34 rows.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...

quote:

---

> hi
> i tried ..but seems some problem.
> SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> the above query returned 34 rows...whereas the below query only 9 rows...i

---

think its using left join bcoz of that..

quote:

---

> SELECT * FROM employee E LEFT JOIN jobs J
> ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> any further updates would be highly appreciated..
> regards
>

---

|||Try below query :
select e.* from employee e, ( SELECT job_id
FROM jobs
WHERE min_lvl <> 25 ) a
where e.job_id = a.job_id
This would increase performance, as for each of the job_id, it wont scan
jobs table.
- Ketan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFu0osW6DHA.2460@.TK2MSFTNGP09.phx.gbl...

quote:

---

> sanjay
> Can post DDL + sample data + expected result and then I can answer you

---

why

quote:

---

> it returns 9 rows and why it returns 34 rows.
>
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
rows...i[QUOTE]
> think its using left join bcoz of that..
>

---

|||sanjay wrote:

quote:

---

> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )

---

1) keep it as it is
2) use EXISTS
SELECT *
FROM Employee
WHERE NOT EXISTS (
SELECT 1
FROM Jobs
WHERE Jobs.job_id = Employee.job_id
AND min_lvl=25
)
3) use OUTER JOIN
SELECT * -- Add DISTINCT if necessary
FROM Employee
LEFT JOIN Jobs
ON Jobs.job_id = Employee.job_id
AND Jobs.min_lvl = 25

quote:

---

> 2) select * from tbla where name like '%F'

---

ALTER TABLE tbla
ADD NameReverse varchar(8000) not null -- use same definition as column
Name
go
UPDATE tbla
SET NameReverse = REVERSE(Name)
go
CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
go
SELECT *
FROM tbla
WHERE NameReverse like REVERSE('F')+'%'
Hope this helps,
Gert-Jan|||Submitted too fast. Of course, the line "WHERE Jobs.job_id IS NULL"
needs to be added to solution 3...
Gert-Jan
Gert-Jan Strik wrote:

quote:

---

> sanjay wrote:
> 1) keep it as it is
> 2) use EXISTS
> SELECT *
> FROM Employee
> WHERE NOT EXISTS (
> SELECT 1
> FROM Jobs
> WHERE Jobs.job_id = Employee.job_id
> AND min_lvl=25
> )
> 3) use OUTER JOIN
> SELECT * -- Add DISTINCT if necessary
> FROM Employee
> LEFT JOIN Jobs
> ON Jobs.job_id = Employee.job_id
> AND Jobs.min_lvl = 25
>
> ALTER TABLE tbla
> ADD NameReverse varchar(8000) not null -- use same definition as column
> Name
> go
> UPDATE tbla
> SET NameReverse = REVERSE(Name)
> go
> CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
> go
> SELECT *
> FROM tbla
> WHERE NameReverse like REVERSE('F')+'%'
> Hope this helps,
> Gert-Jan

---

optimize queries.

how to optimize the following queries.
1) SELECT * FROM employe
WHERE job_id not IN ( SELECT job_i
FROM job
WHERE min_lvl = 25
2) select * from tbla where name like '%F
Regard
sanjaysanjay
It is to hard to say . You did not provide any info about how many data
exists in the table?
How often you query the table?
What is indexes on the table?
My guess.
1) SELECT * FROM employee
WHERE NOT EXISTS
( SELECT jobs .job_id
FROM jobs WHERE employee.job_id
=jobs.job_id AND min_lvl = 25 )
> 2) select * from tbla where name like '%F'
This one may prevent query optimyzer for using an index on the name column
unless you re-write it as 'F%'.
Consider to use covering indexes instead of select * from table .
For more details please refer to BOL
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:19EA7547-8EBC-43FF-9981-3AF635B34A03@.microsoft.com...
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> 2) select * from tbla where name like '%F'
> Regards
> sanjay|||h
im using not In in my query..which as per documents/sqlserver etc..is not the best way to use and it hampers performance...how i can avoide not In.
execution plan of Not In/ Not exists are same.
suppost i ve 1 million records in this tables and its being heavily used.
both are normal tables from pUbs database.
=========2) if we rewrite the query as 'F%' this will change the logic of the query..keeping same logic how can we modify the query|||sanjay
1) Look , another approach but you will have to compare all methods .
1) SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
2)
http://www.sql-server-performance.com/covering_indexes.asp
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFD7096E-56CF-4C1C-B71D-F4862A8EA60D@.microsoft.com...
> hi
> im using not In in my query..which as per documents/sqlserver etc..is not
the best way to use and it hampers performance...how i can avoide not In..
> execution plan of Not In/ Not exists are same..
> suppost i ve 1 million records in this tables and its being heavily used..
> both are normal tables from pUbs database..
> ==========> 2) if we rewrite the query as 'F%' this will change the logic of the
query..keeping same logic how can we modify the query.
>|||hi
i tried ..but seems some problem.
SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
the above query returned 34 rows...whereas the below query only 9 rows...i think its using left join bcoz of that..
SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
any further updates would be highly appreciated..
regards|||sanjay
Can post DDL + sample data + expected result and then I can answer you why
it returns 9 rows and why it returns 34 rows.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> hi
> i tried ..but seems some problem.
> SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> the above query returned 34 rows...whereas the below query only 9 rows...i
think its using left join bcoz of that..
> SELECT * FROM employee E LEFT JOIN jobs J
> ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> any further updates would be highly appreciated..
> regards
>|||Try below query :
select e.* from employee e, ( SELECT job_id
FROM jobs
WHERE min_lvl <> 25 ) a
where e.job_id = a.job_id
This would increase performance, as for each of the job_id, it wont scan
jobs table.
- Ketan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFu0osW6DHA.2460@.TK2MSFTNGP09.phx.gbl...
> sanjay
> Can post DDL + sample data + expected result and then I can answer you
why
> it returns 9 rows and why it returns 34 rows.
>
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> > hi
> > i tried ..but seems some problem.
> > SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> >
> > the above query returned 34 rows...whereas the below query only 9
rows...i
> think its using left join bcoz of that..
> >
> > SELECT * FROM employee E LEFT JOIN jobs J
> > ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> >
> > any further updates would be highly appreciated..
> > regards
> >
>|||sanjay wrote:
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
1) keep it as it is
2) use EXISTS
SELECT *
FROM Employee
WHERE NOT EXISTS (
SELECT 1
FROM Jobs
WHERE Jobs.job_id = Employee.job_id
AND min_lvl=25
)
3) use OUTER JOIN
SELECT * -- Add DISTINCT if necessary
FROM Employee
LEFT JOIN Jobs
ON Jobs.job_id = Employee.job_id
AND Jobs.min_lvl = 25
> 2) select * from tbla where name like '%F'
ALTER TABLE tbla
ADD NameReverse varchar(8000) not null -- use same definition as column
Name
go
UPDATE tbla
SET NameReverse = REVERSE(Name)
go
CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
go
SELECT *
FROM tbla
WHERE NameReverse like REVERSE('F')+'%'
Hope this helps,
Gert-Jan|||Submitted too fast. Of course, the line "WHERE Jobs.job_id IS NULL"
needs to be added to solution 3...
Gert-Jan
Gert-Jan Strik wrote:
> sanjay wrote:
> >
> > how to optimize the following queries..
> > 1) SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> 1) keep it as it is
> 2) use EXISTS
> SELECT *
> FROM Employee
> WHERE NOT EXISTS (
> SELECT 1
> FROM Jobs
> WHERE Jobs.job_id = Employee.job_id
> AND min_lvl=25
> )
> 3) use OUTER JOIN
> SELECT * -- Add DISTINCT if necessary
> FROM Employee
> LEFT JOIN Jobs
> ON Jobs.job_id = Employee.job_id
> AND Jobs.min_lvl = 25
> > 2) select * from tbla where name like '%F'
> ALTER TABLE tbla
> ADD NameReverse varchar(8000) not null -- use same definition as column
> Name
> go
> UPDATE tbla
> SET NameReverse = REVERSE(Name)
> go
> CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
> go
> SELECT *
> FROM tbla
> WHERE NameReverse like REVERSE('F')+'%'
> Hope this helps,
> Gert-Jansql

optimize nologging

Hi,
We have a reporting database with simple recovery model.
To improve performance we have to use SELECT..INTO clause
and create all tables...but problem now is that each
table is populating from 3-4 different result set...so if
we use SELECT ..INTO for first load(we can't use UNION in
SELECT..INTO CLAUSE) then for next 3-4 loads we have to
use INSERT INTO SELECT clause that will do lot of logging.
What are the possible options that we can use in this
scenario?
For temporary solution we are thinking of using SELECT
INTO and create 4 temp tables then bcp out the data and
then use BULK INSERT into origional table --what can be
possible flaws in this scenario?
Thanks
--HarvinderYou can use a derived table in the select statement of the select into, for
example:
SELECT column_1, column_2 INTO new_table
FROM
(SELECT column_1, column_2 FROM table_1
UNION ALL
SELECT column_1, column_2 FROM table_2) AS old_table
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"harvinder" <hs@.metratech.com> wrote in message
news:072401c3787a$cf8048a0$a001280a@.phx.gbl...
> Hi,
> We have a reporting database with simple recovery model.
> To improve performance we have to use SELECT..INTO clause
> and create all tables...but problem now is that each
> table is populating from 3-4 different result set...so if
> we use SELECT ..INTO for first load(we can't use UNION in
> SELECT..INTO CLAUSE) then for next 3-4 loads we have to
> use INSERT INTO SELECT clause that will do lot of logging.
> What are the possible options that we can use in this
> scenario?
> For temporary solution we are thinking of using SELECT
> INTO and create 4 temp tables then bcp out the data and
> then use BULK INSERT into origional table --what can be
> possible flaws in this scenario?
> Thanks
> --Harvinder
>

Optimize "LIKE"

It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.

How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.

Thank you.

You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.

Using '%word%' will always scan every row in the entire table.

Hope this assists.

Optimize "LIKE"

It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.

How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.

Thank you.

You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.

Using '%word%' will always scan every row in the entire table.

Hope this assists.