Re: Problems when using TOP statement
From: John Kane (jt-kane_at_comcast.net)
Date: 01/13/05
- Next message: Hilary Cotter: "Re: FullText Search data model for speed"
- Previous message: John Kane: "Re: FullText Search data model for speed"
- In reply to: Henke: "Re: Problems when using TOP statement"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 13 Jan 2005 08:24:17 -0800
Henke,
Could you post the output of the following SQL script as an attached .txt or
.rpt file as to understand pure T-SQL query performance issues that use
T-SQL LIKE (with no preceding wildcard in the search string), more
information is necessary to understand the SQL Server query optimizer's
selections. Note, the attached SQL table scripts do not indicate what
indexes are on each table:
use <your_database_name_here>
go
select @@version
select @@language
go
Set statistics profile on
Set statistics IO on
Set statistics time on
Go
-- your query here...
Set statistics profile off
Set statistics IO off
Set statistics time off
Go
SET SHOWPLAN_TEXT ON
go
-- your query here...
go
SET SHOWPLAN_TEXT OFF
GO
exec sp_help Customer
exec sp_help RealEstate
exec sp_help Building
exec sp_help InstallationAddress
exec sp_help ConnectionAddress
go
Thanks,
John
-- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ "Henke" <henke_nord@hotmail.com> wrote in message news:#IapG6W#EHA.3416@TK2MSFTNGP09.phx.gbl... > Hi, and thanks for your answer! Here is the query and a script that I hope > can generate the parts of our database that is involved. > SELECT TOP 20 dbo.SalesProject.SalesProjectID AS SPV_SalesProjectID, > dbo.SalesProject.SalesProjectNumber AS SPV_SalesProjectNumber, > dbo.SalesProject.SalesProjectName AS SPV_SalesProjectName, > dbo.SalesProject.CustomerID AS SPV_CustomerID, > dbo.SalesProject.SalesProjectTypeID AS SPV_SalesProjectTypeID, > dbo.SalesProject.PhaseID AS SPV_PhaseID, dbo.Customer.CustomerID AS > CV_CustomerID, dbo.Customer.CustomerNumber AS CV_CustomerNumber, > dbo.Customer.CustomerName AS CV_CustomerName, > dbo.Customer.CustomerBusinessGroupID AS CV_CustomerBusinessGroupID, > dbo.Customer.OrganisationNumber AS CV_OrganisationNumber, > dbo.Customer.Street AS CV_Street, dbo.Customer.ZipCode AS CV_ZipCode, > dbo.Customer.City AS CV_City > FROM dbo.Customer LEFT OUTER JOIN > dbo.SalesProject ON dbo.Customer.CustomerID = dbo.SalesProject.CustomerID > WHERE (dbo.SalesProject.SalesProjectID IN > (SELECT SalesprojectID > FROM RealEstate > WHERE (RealEstateID IN > (SELECT Building.RealestateID > FROM Building > WHERE BuildingID IN > (SELECT BuildingID > FROM InstallationAddress > WHERE InstallationAddress.Street LIKE 'drottning%' AND > Replace(InstallationAddress.ZipCode, ' ', '') LIKE '65224%' AND > InstallationAddress.City LIKE 'karlstad%'))))) > OR(dbo.SalesProject.SalesProjectID IN > (SELECT SalesProjectID > FROM ConnectionAddress > WHERE ConnectionAddress.Street LIKE 'drottning%' AND > Replace(ConnectionAddress.ZipCode, ' ', '') LIKE '65224%' AND > ConnectionAddress.City LIKE 'karlstad%')) > > /Henke > > "Hilary Cotter" <hilary.cotter@gmail.com> skrev i meddelandet > news:uBC7hnW%23EHA.2112@TK2MSFTNGP14.phx.gbl... > > absolutely not. Can you post your query, and possibly your table schema. > > > > -- > > Hilary Cotter > > Looking for a SQL Server replication book? > > http://www.nwsu.com/0974973602.html > > "Henke" <henke_nord@hotmail.com> wrote in message > > news:OPz3QwV%23EHA.208@TK2MSFTNGP12.phx.gbl... > >> Hi! > >> I have a query that takes about 3 seconds to process. When I add TOP 20 > >> to > >> reduce the number of hits the same query takes 18 seconds. Is this a > > correct > >> behaviour? > >> > >> /Henke > >> > >> > > > > > > >
- Next message: Hilary Cotter: "Re: FullText Search data model for speed"
- Previous message: John Kane: "Re: FullText Search data model for speed"
- In reply to: Henke: "Re: Problems when using TOP statement"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
