Re: Problems when using TOP statement

From: John Kane (jt-kane_at_comcast.net)
Date: 01/13/05 

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
> >>
> >>
> >
> >
>
>
>

Relevant Pages

  • Okay With Pass Through But Getting Error
    ... I have a query in design view, clicked on the SQL button, ... ADO Error: Incorrect syntax near the keyword 'EXEC' ...
    (microsoft.public.sqlserver.programming)
  • Re: Building queries as strings
    ... In one of my stored procedures, the business layer is passing an ... Is there a way of doing this rather than creating the query as a string ... exec a system stored procedure called sp_executeSQL ... DECLARE @sql nvarchar ...
    (microsoft.public.sqlserver)
  • Re: Help with a query
    ... Could you post back with the full output of the following SQL code as this ... EXEC sp_help_fulltext_catalogs ... have you tried using the WEIGHT parameter on the title column? ... > I'm new to fulltext search and am having a problem formulating a query. ...
    (microsoft.public.sqlserver.fulltext)
  • Slowness after upgrading to SQL 2000
    ... We have upgrade from SQL 7.0 to SQL 2000. ... All these query are dynamically generated using Exec. ...
    (microsoft.public.sqlserver.programming)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
Quantcast