Performance Problem

Hi,
This is query that is causing problem
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
WHERE WO.AssignedTo = 1020
AND StateMaster.IsInDashboard = 1
1. WorkOrder table is master table which consists of 155986 rows.
2. Task table is the child table refering to workorder (Id) which is
having 516060 rows.
3. Statemaster is the master table consists of about 500 rows.
Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
the condition StateMaster.IsInDashboard = 1 the result set
will be minimized to 30 rows.
For executing the above query it is taking 1.7 sec.
This is the execution Plan i got when i run this query
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
| |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
| | |--Index
S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
| |--Clustered Index
S(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
SEEK:([StateMaster].[Id]=[WO].[StatusId]),
WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
|--Index
S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
I am not understanding why it is doing bookmark lookup on workorder
table when i joined StatusId column with Id column in statemaster table
and checking the condition Statemaster.IsinDashboard = 1.
These are the indexes we have on these tables.
1. In Task table "Id" is the primary Key and it is having
non-clustered index on workorderid
2. In Workorder table "Id" is the primary Key and it is having
non-clustered index on statusid
3. In Statemaster table "Id" is the primary Key
Could anyone help me out why the bookmark lookup is happening because
bookmark lookup on workorder table is taking above 95% of the total
query time.
Regards,
Ramnadh.
*** Sent via Developersdex http://www.examnotes.net ***I'd try creating an indexed view with the "StateMaster.IsInDashboard = 1"
condition on the dbo.StateMaster table.
I.e.:
create view <view name>
with schemabinding
as
select dbo.StateMaster.Id
,<column list>
from dbo.StateMaster
where (dbo.StateMaster.IsInDashboard = 1)
go
create unique clustered index <index name>
on <view name>
(
Id
)
go
Also consider using the READPAST hint instead of the notorious NOCOUNT hint.
Look up the details in Books Online.
ML
http://milambda.blogspot.com/|||> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
Does the StatusId column in WorkOrder table have an index?
"ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
news:e5p573KLGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> This is query that is causing problem
> SELECT T.Id AS TaskId
> FROM dbo.Task T (NOLOCK)
> INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
> INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
> WHERE WO.AssignedTo = 1020
> AND StateMaster.IsInDashboard = 1
> 1. WorkOrder table is master table which consists of 155986 rows.
> 2. Task table is the child table refering to workorder (Id) which is
> having 516060 rows.
> 3. Statemaster is the master table consists of about 500 rows.
> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
> the condition StateMaster.IsInDashboard = 1 the result set
> will be minimized to 30 rows.
> For executing the above query it is taking 1.7 sec.
>
> This is the execution Plan i got when i run this query
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
> | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
> OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
> | | |--Index
> S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
> SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
> | |--Clustered Index
> S(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
> SEEK:([StateMaster].[Id]=[WO].[StatusId]),
> WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
> |--Index
> S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
> SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
>
> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
> These are the indexes we have on these tables.
> 1. In Task table "Id" is the primary Key and it is having
> non-clustered index on workorderid
> 2. In Workorder table "Id" is the primary Key and it is having
> non-clustered index on statusid
> 3. In Statemaster table "Id" is the primary Key
> Could anyone help me out why the bookmark lookup is happening because
> bookmark lookup on workorder table is taking above 95% of the total
> query time.
> Regards,
> Ramnadh.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows
Since you have no index on AssigendTo, indexes can't be used to satisfy this
predicate and I suspect IsinDashboard isn't very selective. I suggest you
create a composite nonclustered index on AssigendTo and IsinDashboard.
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFLTGNLLGHA.2276@.TK2MSFTNGP15.phx.gbl...
> Does the StatusId column in WorkOrder table have an index?
>
> "ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
> news:e5p573KLGHA.1424@.TK2MSFTNGP12.phx.gbl...
>|||Yep,Dan, I have missed that AssigendTo column has no index.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhRp1RLLGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Since you have no index on AssigendTo, indexes can't be used to satisfy
> this predicate and I suspect IsinDashboard isn't very selective. I
> suggest you create a composite nonclustered index on AssigendTo and
> IsinDashboard.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eFLTGNLLGHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||I had defined a view by giving the query
create view taskview with schemabinding as
select dbo.StateMaster.Id
, IsTerminal
from dbo.StateMaster
where StateMaster.IsInDashboard = 1
and statemaster.isterminal = 0
and i had defined the clustered index on Id column of the view.it worked
fine and it was reduced to 400 ms.
1) How does the optimizer avoided the bookmark lookup by creating a view
and defining an index on the Id column .
2) i am having another problem when i added another table in the join
SELECT T.Id AS TaskId FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN taskview (NOLOCK) ON taskview.Id = WO.StatusId
inner join objectdetail OD(nolock) on wo.id = OD.objectid
WHERE WO.AssignedTo = 1020
and taskview.isterminal = 0
and OD.objecttypeid = 10
OR ( WO.AssignedTo = 1020 AND ISNULL(taskview.IsTerminal,0) = 0
AND( CONVERT(CHAR(8),'12/01/2005',112) <= CONVERT(CHAR(8),
DATEADD(DD,1,OD.LastStatusUpdDt) , 112) )
)
OR ( WO.AssignedTo = 1020 AND ISNULL(taskview.IsTerminal,0) = 1
AND( CONVERT(CHAR(8),'12/01/2005',112) <= CONVERT(CHAR(8) ,
OD.LastStatusUpdDt , 112) )
)
(ObjectTypeId,ObjectId) is the primary key on ObjectDetail table.It
consists of around 228313 rows. The condition Objecttypeid = 10 satifies
around 156000 rows.
It was taking above 3 sec for retrieving the 30 rows. The execution plan
is showing as below
|--Nested Loops(Inner Join, OUTER REFERENCES:([OD].[ObjectId]) WITH
PREFETCH)
|--Hash Match(Inner Join, HASH:([WO].[Id])=([OD].[ObjectId]),
RESIDUAL:(((Convert([taskview].[IsTerminal])=0 AND
[OD].[ObjectTypeId]=10) OR (Convert([taskview].[IsTerminal])=0 AND
'12/01/20'<=Convert(dateadd(day, 1, [OD].[LastStatusUpdDt])))) OR (Co
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([WO].[StatusId]))
| | |--Hash Match(Inner Join, HASH:([WO].[Id])=([WO].[Id]))
| | | |--Index
S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=1020) ORDERED FORWARD)
| | | |--Index
Scan(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder_StatusId] AS
[WO]))
| | |--Clustered Index
S(OBJECT:([Viper63].[dbo].[taskview].[taskid]),
SEEK:([taskview].[Id]=[WO].[StatusId]),
WHERE:(Convert([taskview].[IsTerminal])=0 OR
Convert([taskview].[IsTerminal])=1) ORDERED FORWARD)
| |--Clustered Index
Scan(OBJECT:([Viper63].[dbo].[ObjectDetail].[PK_ObjectDetail] AS [OD]),
WHERE:(([OD].[ObjectTypeId]=10 OR '12/01/2005'<=Convert(dateadd(day, 1,
[OD].[LastStatusUpdDt]))) OR
'12/01/2005'<=Convert([OD].[LastStatusUpdDt])))
|--Index
S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[OD].[ObjectId]) ORDERED FORWARD)
It was doing clustered Index scan on above 2 million rows of the
ObjectDetail table which is taking above 58% of the total query time.
I had defined Non-Clustered Index on "LastStatusUpdDt" column in the
Objectdetail table. It reduced to around 2 sec. It is showing the same
execution plan but now Clustered Index s on Task table and the first
hash match join are taking most of the query time. I am not getting why
the hashmatch join and clustered index s is taking much time in
executing the query.
Thanks a lot for the help.
*** Sent via Developersdex http://www.examnotes.net ***|||A few basic suggestions:
- where status columns are used (e.g. in your case "isTerminal",
"IsInDashboard") and values have low selectivity, consider adding indexed
views for each status value (as I've illustrated in a previous post). This
will improve performance of queries where a specific value for this status
column is used as a restrictive condition;
- avoid using functions in conditions (such as the ISNULL and CONVERT in
your where clause), especially if other methods can be used, since functions
cause scans, wile other methods enable use of indexes;
- avoid complex OR conditions - try using UNION - e.g.:
select ... from
where <one set of conditions>
union --all -- use all or not - depending on actual requirements (test,
test, test...)
select ... from
where <other set of conditions>
As Uri and Dan suggested - put an index on the AssignedTo column. A
nonclustered would do.
ML
http://milambda.blogspot.com/|||Thanks for the information. I left out the basic concepts of using
functions in the where clause which will affect the performance. thanks
for reminding me. i will check by removing the functions which will
enable using of indexes by engine and will check by replacing OR
condition with the union also in the query which will affect performance
badly.
Sorry Uri and Dan for not giving the complete information. there is a
non-clustered non-unique index on assignedto column on the workorder
table.
*** Sent via Developersdex http://www.examnotes.net ***|||I had changed the query by removing usage of functions.
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN taskview ON taskview.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE ( WO.AssignedTo = 1020 and ((taskview.IsTerminal = 1 AND
@.seldate <= OD.LastStatusUpdDt )
or (taskview.IsTerminal = 0 AND @.seldate <=
OD.LastStatusUpdDt+1 )
)
)
Replaced OR with the UNION using below query
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN TaskView ON TaskView.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = 1020
AND Taskview.IsTerminal = 0
AND @.seldate <= OD.LastStatusUpdDt + 1
UNION
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN TaskView ON TaskView.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = 1020
AND taskview.IsTerminal = 1
AND @.seldate <= OD.LastStatusUpdDt
i replaced the OR condition with UNION, but i am seeing same execution
plan using by optimizer.below is the execution plan generated
|--Nested Loops(Inner Join, OUTER REFERENCES:([OD].[ObjectId]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id],
[TASKVIEW].[isterminal]) WITH PREFETCH)
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([WO].[StatusId]))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Changeware].[dbo].[WorkOrder] AS [WO]))
| | | |--Index
S(OBJECT:([Changeware].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=1020) ORDERED FORWARD)
| | |--Clustered Index
S(OBJECT:([Changeware].[dbo].[TASKVIEW].[taskindex]),
SEEK:([TASKVIEW].[id]=[WO].[StatusId]),
WHERE:(Convert([TASKVIEW].[isterminal])=1 OR
Convert([TASKVIEW].[isterminal])=0) ORDERED FORWARD)
| |--Clustered Index
S(OBJECT:([Changeware].[dbo].[ObjectDetail].[PK_ObjectDetail] AS
[OD]), SEEK:([OD].[ObjectTypeId]=10 AND [OD].[ObjectId]=[WO].[Id]),
WHERE:(((Convert([TASKVIEW].[isterminal])=1 AND
[@.seldate]<=[OD].[LastStatusUpdDt]) OR (
|--Index
S(OBJECT:([Changeware].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[OD].[ObjectId]) ORDERED FORWARD)
It is doing bookmark lookup on the workorder table which is costing
around 92% of the total query cost. I am still not getting, with the
proper indexes on the statusid column and the assignedto column of the
workorder table why optimizer is doing bookmark lookup on workorder
table.
*** Sent via Developersdex http://www.examnotes.net ***|||have you tried indexes on WorkOrder(Id, AssignedTo) and
WorkOrder(AssignedTo, iD) ?

Performance Problem

Hi,
Below is the query that causing problems

SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
WHERE WO.AssignedTo = 1020
AND StateMaster.IsInDashboard = 1

1. WorkOrder table is master table which consists of 155986 rows.
2. Task table is the child table refering to workorder (Id) which is
having 516060 rows.
3. Statemaster is the master table consists of about 500 rows.

Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
the condition StateMaster.IsInDashboard = 1 the result set
will be minimized to 30 rows.

For executing the above query it is taking 1.7 sec.

This is the execution Plan i got when i run this query

|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
| |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
| | |--Index
Seek(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
| |--Clustered Index
Seek(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
SEEK:([StateMaster].[Id]=[WO].[StatusId]),

WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)

I am not understanding why it is doing bookmark lookup on workorder
table when i joined StatusId column with Id column in statemaster table
and checking the condition Statemaster.IsinDashboard = 1.

These are the indexes we have on these tables.

1. In Task table "Id" is the primary Key and it is having
non-clustered index on workorderid
2. In Workorder table "Id" is the primary Key and it is having
non-clustered index on statusid
3. In Statemaster table "Id" is the primary Key

Could anyone help me out why the bookmark lookup is happening, it is
taking about 95% of the query time.

Regards,
ramnadh.

*** Sent via Developersdex http://www.developersdex.com ***This question has been answered in microsoft.public.sqlserver.programming.
Please don't post the same question independently to multiple groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
news:hLlGf.1$gp2.651@.news.uswest.net...
> Hi,
> Below is the query that causing problems
> SELECT T.Id AS TaskId
> FROM dbo.Task T (NOLOCK)
> INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
> INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
> WHERE WO.AssignedTo = 1020
> AND StateMaster.IsInDashboard = 1
> 1. WorkOrder table is master table which consists of 155986 rows.
> 2. Task table is the child table refering to workorder (Id) which is
> having 516060 rows.
> 3. Statemaster is the master table consists of about 500 rows.
> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
> the condition StateMaster.IsInDashboard = 1 the result set
> will be minimized to 30 rows.
> For executing the above query it is taking 1.7 sec.
>
> This is the execution Plan i got when i run this query
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
> | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
> OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
> | | |--Index
> Seek(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
> SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
> | |--Clustered Index
> Seek(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
> SEEK:([StateMaster].[Id]=[WO].[StatusId]),
> WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
> |--Index
> Seek(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
> SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
>
> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
> These are the indexes we have on these tables.
> 1. In Task table "Id" is the primary Key and it is having
> non-clustered index on workorderid
> 2. In Workorder table "Id" is the primary Key and it is having
> non-clustered index on statusid
> 3. In Statemaster table "Id" is the primary Key
> Could anyone help me out why the bookmark lookup is happening, it is
> taking about 95% of the query time.
> Regards,
> ramnadh.
> *** Sent via Developersdex http://www.developersdex.com ***

Performance of SPROC changed by dbo. prefix

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

Edward<teddysnips@.hotmail.comwrote in message
news:1171898428.745133.216550@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.
>

dbo = database owner.

This is actually fairly common.

So not really sure what you're finding unusual here.

However, that said, you should call all stored procs with the owner
qualifier included.

Example:

stored proc FOO

Created by the sa so it's qualifed as:

dbo.FOO

Now user BAR comes along and calls:

exec FOO

First SQL Server will check to see if there is a stored proc BAR.FOO and try
to execute that. If not, THEN it'll look up dbo.FOO and try to execute
that.

Sounds like what's happening here. (Or something similar.) (note it's even
worse if it's named sp_xxxx).

Not sure why it would take 4-10 seconds, but I suspect that's part of the
issue.

Quote:

Originally Posted by

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.
>
Any thoughts?
>
Edward
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>
In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.
>
Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.
>
Any thoughts?
>
Edward

Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.

Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).

Gert-Jan|||(teddysnips@.hotmail.com) writes:

Quote:

Originally Posted by

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.
>
Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.
>
Any thoughts?

All stored procedures in a database (and all tables, all views etc)
belongs to a schema, and the full name within the database is
schema.procedure. If you leave out the schema when you create your
procedure, the procedure is created in your default schema. If you are
the database owner the default is "dbo". On SQL 2000, your default
schema is always the same as your user name. But in SQL 2005, owners
and schema are separeate, and all users can have dbo as their default
schema.

In many databases, all objects are in the dbo schema.

It follows from this, that whatever the performance problems with
your procedure due to, it was not the dbo prefix. (Unless you recreated
the procedure in your default schema which have tables that are
namesakes with those in the dbo schema - but are much smaller.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nlwrote in message
news:45DA261B.75D850BB@.toomuchspamalready.nl...

Quote:

Originally Posted by

teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>>
>In a system I'm maintaining there is a Stored Procedure called
>dbo.MyStoredProcedure. I didn't create this - it was created by a
>developer who has now left. I don't know how the object came by its
>"dbo." prefix, but I think he created it in QA.
>>
>Anyway, there were some performance issues (it was taking between 4
>and 10 seconds to complete) so I copied the SQL into a QA window and
>it consistently ran in under 1 second. So I created a new SPROC with
>SQL exactly identical to the old one, but without the "dbo." prefix,
>and that too runs in <1 second.
>>
>Any thoughts?
>>
>Edward

>
Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.
>
Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).
>

Duh, didn't even think of recompilation.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Quote:

Originally Posted by

Gert-Jan

|||On 19 Feb, 22:35, Gert-Jan Strik <s...@.toomuchspamalready.nlwrote:

Quote:

Originally Posted by

teddysn...@.hotmail.com wrote:
>

Quote:

Originally Posted by

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

>

Quote:

Originally Posted by

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

>

Quote:

Originally Posted by

Any thoughts?

>

Quote:

Originally Posted by

Edward

>
Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.
>
Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).

10/10! I dropped the SPROC, then recreated it identically and it ran
like a greyhound. So all it needed was recompilation. I guess
there's an art to knowing how often/in what circumstances to recompile
SPROCs but I'm a developer, not a DBA so I don't know!

Thanks

Edward|||(teddysnips@.hotmail.com) writes:

Quote:

Originally Posted by

10/10! I dropped the SPROC, then recreated it identically and it ran
like a greyhound. So all it needed was recompilation. I guess
there's an art to knowing how often/in what circumstances to recompile
SPROCs but I'm a developer, not a DBA so I don't know!

You don't even have to drop the procedure, it sufficient to say:

sp_recompile procname

to flush all plans of it out the cache.

Normally, this is not needed, but SQL Server has this feature known as
parameter sniffing. When the optimizer builds the plan on the first
invocation, it looks at the actual parameter values and takes this as
guidance. But if that first invocation is for an untypical value, that
may buy you a plan which is bad for regular input.

This is not the only reason for a this sort of behaviour. It can also
be that the statistics are such that the optimizer's estimates for
two plans are very close, although one of the plans are not good at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Performance of query joining tables linked between different datab

The following query takes almost two minutes to run:
SELECT Reference.dbo.tlkpInstruments.IDInstrument,
Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
FROM dbo.tblAccounts LEFT OUTER JOIN
Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
Reference.dbo.tlkpInstruments.IDInstrument
tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
All rows retrieved in less than 5 seconds.
tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
All rows retrieved in less than 5 seconds.
Why is the query running so slowly?
Thanks.
The linked column in the subsidiary table is the primary key of that table
and is indexed that way. In the main table, I've tried indexing the linked
column separately and adding it to the primary key. Neither approach makes
any substantial change.
I've also tried importing the subsidiary table into the main database. There
is no change to the performance.
Anything else I can try?
"Tibor Karaszi" wrote:

> Start by adding indexes on the columns that you do the join over. Also consider making one or even
> both of the indexes clustered indexes (assuming that is suitable).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:5FF860E0-152C-4C5C-AB90-1FA78D9E05B1@.microsoft.com...
>
|||99% chance it's the way you've indexed the tables.
Post the DDL (CREATE TABLE and CREATE INDEX statements) and someone can
probably help.
-Dave
TLD wrote:[vbcol=seagreen]
> The linked column in the subsidiary table is the primary key of that table
> and is indexed that way. In the main table, I've tried indexing the linked
> column separately and adding it to the primary key. Neither approach makes
> any substantial change.
> I've also tried importing the subsidiary table into the main database. There
> is no change to the performance.
> Anything else I can try?
> "Tibor Karaszi" wrote:
-Dave Markle
http://www.markleconsulting.com/blog
|||TLD, I don't see a thing wrong with your query from a performance
perspective.
Try this:
SELECT
i.IDInstrument,
i.Instrument,
a.*
into #bleh
FROM dbo.tblAccounts a
LEFT JOIN dbo.tlkpInstruments i
ON a.Commodities = i.IDInstrument;
DROP TABLE #bleh;
How long does that take? If it's quick, it probably indicates that you
have a problem with the speed of returning rows to the client. This can
be caused by a network configuration issue or by having some task on the
client side that's eating up CPU cycles.
What happens when you put this in front of your query? What are the
results in your "Messages" window?
SET STATISTICS IO ON;
-Dave
TLD wrote:[vbcol=seagreen]
> Here are table and index definitions:
>
> CREATE TABLE [dbo].[tblAccounts] (
> [AccID] [bigint] NOT NULL ,
> [OffID] [int] NOT NULL ,
> [ID] [bigint] NULL ,
> [AccNo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AccName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [tinyint] NULL ,
> [3rdPartyGty] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Division] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [USRating] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UKRating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InitReqmnts] [int] NULL ,
> [StressExposure] [int] NULL ,
> [MarDefExcess] [int] NULL ,
> [ARTL] [int] NULL ,
> [SRTL] [int] NULL ,
> [IMTL] [int] NULL ,
> [PCOvr] [tinyint] NULL ,
> [Action] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EvalDate] [smalldatetime] NULL ,
> [RenewDate] [smalldatetime] NULL ,
> [LevelPrevious] [int] NULL ,
> [EvalDatePrevious] [smalldatetime] NULL ,
> [Note] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Exclude] [bit] NULL ,
> [NetLiquidity] [int] NULL ,
> [MaxRisk] [int] NULL ,
> [NotAmt] [int] NULL ,
> [MarReqd] [int] NULL ,
> [Country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHEquity] [int] NULL ,
> [EstProfit] [int] NULL ,
> [Exposure] [numeric](10, 2) NULL ,
> [Options] [bit] NULL ,
> [Contracts] [int] NULL ,
> [Symbol] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PriceCurrent] [money] NULL ,
> [PriceHistoric] [money] NULL ,
> [Documentation] [bit] NULL ,
> [Commodities] [int] NULL ,
> [Comment] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MD] [bit] NULL ,
> [Diversified] [smallint] NULL ,
> [Doc] [smallint] NULL ,
> [Credit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Legal] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accepted] [smallint] NULL ,
> [Man] [bit] NULL ,
> [TriggerContracts] [int] NULL ,
> [Inst] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> CREATE UNIQUE CLUSTERED
> INDEX [PK_tblAccounts] ON [dbo].[tblAccounts] ([AccID], [OffID],
> [Commodities])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> CREATE TABLE [dbo].[tlkpInstruments] (
> [IDInstrument] [int] IDENTITY (1, 1) NOT NULL ,
> [Instrument] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExchangeID] [int] NOT NULL ,
> [GroupID] [int] NULL ,
> [Collective] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE UNIQUE CLUSTERED
> INDEX [PK_tlkpInstruments] ON [dbo].[tlkpInstruments] ([IDInstrument])
> WITH
> DROP_EXISTING
> ON [PRIMARY]
>
> "TLD" wrote:
-Dave Markle
http://www.markleconsulting.com/blog
|||This is a local instance of SServer. There is no network connection. The only
client running is Query Analyzer.
The SELECT... INTO took one second.
Re-running the query takes 1m54s:
Application Profile Statistics
Timer resolution (milliseconds)00
Number of INSERT, UPDATE, DELETE statements00
Rows effected by INSERT, UPDATE, DELETE statements00
Number of SELECT statements11
Rows effected by SELECT statements4334843348
Number of user transactions22
Average fetch time00
Cumulative fetch time00
Number of fetches00
Number of open statement handles00
Max number of opened statement handles00
Cumulative number of statement handles00
Network Statistics
Number of server roundtrips11
Number of TDS packets sent11
Number of TDS packets received27092709
Number of bytes sent644644
Number of bytes received1.10936e+0071.10936e+007
Time Statistics
Cumulative client processing time00
Cumulative wait time on server replies6464
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:

> TLD, I don't see a thing wrong with your query from a performance
> perspective.
> Try this:
> SELECT
> i.IDInstrument,
> i.Instrument,
> a.*
> into #bleh
> FROM dbo.tblAccounts a
> LEFT JOIN dbo.tlkpInstruments i
> ON a.Commodities = i.IDInstrument;
> DROP TABLE #bleh;
> How long does that take? If it's quick, it probably indicates that you
> have a problem with the speed of returning rows to the client. This can
> be caused by a network configuration issue or by having some task on the
> client side that's eating up CPU cycles.
> What happens when you put this in front of your query? What are the
> results in your "Messages" window?
> SET STATISTICS IO ON;
> -Dave
>
> TLD wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
>
|||The two minutes is slow, of course,
But your "5 seconds" timings are suspect, too.
Are you running on a tiny, tiny machine, or on a busy server with just
all kinds of other stuff going on?
You ought to be able to fetch both tables and the join in five seconds
easy, even with no keys at all, on anything like a current machine.
J.
On Fri, 26 Jan 2007 09:38:01 -0800, TLD
<TLD@.discussions.microsoft.com> wrote:

>The following query takes almost two minutes to run:
>SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
>Reference.dbo.tlkpInstruments.IDInstrument
>tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>All rows retrieved in less than 5 seconds.
>tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>All rows retrieved in less than 5 seconds.
>Why is the query running so slowly?
>Thanks.
|||I'm using a dual-Opteron 64 workstation with 2G of memory. SQL Server is
installed as a local instance. There is no active network connection.
"JXStern" wrote:

> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
>
>
|||I should add that the performance is the same when running with an Access
front-end in a server environment. This is why I'm trying to get it
straightened out on a local machine first. The speed of return of the tables
by themselves is less than five seconds, more like three.
This shouldn't make any difference but there are many more records in the
main table without an associated record in the subsidiary than there are with
one. That brings up a lot of NULLs.
What about the order of the fields in the join? Could that make any
difference?
"JXStern" wrote:

> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
>
>
|||Here is an answer:
The field which is causing the delay is
TriggerContracts int 4
When all except this field are accessed with the join, the return is less
than five seconds. When this field is included the return is two minutes.
Bracketing the name doesn't change the performance. Changing the name to
TContracts doesn't change the performance. Add the field to the clustered
index doesn't change the performance. What in the world is going on?
"JXStern" wrote:

> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
>
>
|||The problem has been solved. By reworking my front end, I was able to
eliminate some of the fields in the query. Now it runs in less than five
seconds.
Thanks to all for your help.
"JXStern" wrote:

> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
>
>

Performance of query joining tables linked between different datab

The following query takes almost two minutes to run:
SELECT Reference.dbo.tlkpInstruments.IDInstrument,
Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
FROM dbo.tblAccounts LEFT OUTER JOIN
Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities = Reference.dbo.tlkpInstruments.IDInstrument
tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
All rows retrieved in less than 5 seconds.
tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
All rows retrieved in less than 5 seconds.
Why is the query running so slowly?
Thanks.Start by adding indexes on the columns that you do the join over. Also consider making one or even
both of the indexes clustered indexes (assuming that is suitable).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:5FF860E0-152C-4C5C-AB90-1FA78D9E05B1@.microsoft.com...
> The following query takes almost two minutes to run:
> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => Reference.dbo.tlkpInstruments.IDInstrument
> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> All rows retrieved in less than 5 seconds.
> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> All rows retrieved in less than 5 seconds.
> Why is the query running so slowly?
> Thanks.|||The linked column in the subsidiary table is the primary key of that table
and is indexed that way. In the main table, I've tried indexing the linked
column separately and adding it to the primary key. Neither approach makes
any substantial change.
I've also tried importing the subsidiary table into the main database. There
is no change to the performance.
Anything else I can try?
"Tibor Karaszi" wrote:
> Start by adding indexes on the columns that you do the join over. Also consider making one or even
> both of the indexes clustered indexes (assuming that is suitable).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:5FF860E0-152C-4C5C-AB90-1FA78D9E05B1@.microsoft.com...
> > The following query takes almost two minutes to run:
> >
> > SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> > Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> > FROM dbo.tblAccounts LEFT OUTER JOIN
> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => > Reference.dbo.tlkpInstruments.IDInstrument
> >
> > tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> > All rows retrieved in less than 5 seconds.
> >
> > tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> > All rows retrieved in less than 5 seconds.
> >
> > Why is the query running so slowly?
> >
> > Thanks.
>|||Having the tables cross databases shouldn't affect performance (but cross instances would). Next
step would be considering what index that should be the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:A384955A-EB0A-43E1-B2DE-A03768D317E1@.microsoft.com...
> The linked column in the subsidiary table is the primary key of that table
> and is indexed that way. In the main table, I've tried indexing the linked
> column separately and adding it to the primary key. Neither approach makes
> any substantial change.
> I've also tried importing the subsidiary table into the main database. There
> is no change to the performance.
> Anything else I can try?
> "Tibor Karaszi" wrote:
>> Start by adding indexes on the columns that you do the join over. Also consider making one or
>> even
>> both of the indexes clustered indexes (assuming that is suitable).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "TLD" <TLD@.discussions.microsoft.com> wrote in message
>> news:5FF860E0-152C-4C5C-AB90-1FA78D9E05B1@.microsoft.com...
>> > The following query takes almost two minutes to run:
>> >
>> > SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>> > Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>> > FROM dbo.tblAccounts LEFT OUTER JOIN
>> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =>> > Reference.dbo.tlkpInstruments.IDInstrument
>> >
>> > tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>> > All rows retrieved in less than 5 seconds.
>> >
>> > tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>> > All rows retrieved in less than 5 seconds.
>> >
>> > Why is the query running so slowly?
>> >
>> > Thanks.
>>|||99% chance it's the way you've indexed the tables.
Post the DDL (CREATE TABLE and CREATE INDEX statements) and someone can
probably help.
-Dave
TLD wrote:
> The linked column in the subsidiary table is the primary key of that table
> and is indexed that way. In the main table, I've tried indexing the linked
> column separately and adding it to the primary key. Neither approach makes
> any substantial change.
> I've also tried importing the subsidiary table into the main database. There
> is no change to the performance.
> Anything else I can try?
> "Tibor Karaszi" wrote:
>> Start by adding indexes on the columns that you do the join over. Also consider making one or even
>> both of the indexes clustered indexes (assuming that is suitable).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "TLD" <TLD@.discussions.microsoft.com> wrote in message
>> news:5FF860E0-152C-4C5C-AB90-1FA78D9E05B1@.microsoft.com...
>> The following query takes almost two minutes to run:
>> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>> FROM dbo.tblAccounts LEFT OUTER JOIN
>> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =>> Reference.dbo.tlkpInstruments.IDInstrument
>> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>> All rows retrieved in less than 5 seconds.
>> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>> All rows retrieved in less than 5 seconds.
>> Why is the query running so slowly?
>> Thanks.
-Dave Markle
http://www.markleconsulting.com/blog|||Here are table and index definitions:
CREATE TABLE [dbo].[tblAccounts] (
[AccID] [bigint] NOT NULL ,
[OffID] [int] NOT NULL ,
[ID] [bigint] NULL ,
[AccNo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [tinyint] NULL ,
[3rdPartyGty] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USRating] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKRating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InitReqmnts] [int] NULL ,
[StressExposure] [int] NULL ,
[MarDefExcess] [int] NULL ,
[ARTL] [int] NULL ,
[SRTL] [int] NULL ,
[IMTL] [int] NULL ,
[PCOvr] [tinyint] NULL ,
[Action] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EvalDate] [smalldatetime] NULL ,
[RenewDate] [smalldatetime] NULL ,
[LevelPrevious] [int] NULL ,
[EvalDatePrevious] [smalldatetime] NULL ,
[Note] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exclude] [bit] NULL ,
[NetLiquidity] [int] NULL ,
[MaxRisk] [int] NULL ,
[NotAmt] [int] NULL ,
[MarReqd] [int] NULL ,
[Country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHEquity] [int] NULL ,
[EstProfit] [int] NULL ,
[Exposure] [numeric](10, 2) NULL ,
[Options] [bit] NULL ,
[Contracts] [int] NULL ,
[Symbol] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PriceCurrent] [money] NULL ,
[PriceHistoric] [money] NULL ,
[Documentation] [bit] NULL ,
[Commodities] [int] NULL ,
[Comment] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MD] [bit] NULL ,
[Diversified] [smallint] NULL ,
[Doc] [smallint] NULL ,
[Credit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Legal] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accepted] [smallint] NULL ,
[Man] [bit] NULL ,
[TriggerContracts] [int] NULL ,
[Inst] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED
INDEX [PK_tblAccounts] ON [dbo].[tblAccounts] ([AccID], [OffID],
[Commodities])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
CREATE TABLE [dbo].[tlkpInstruments] (
[IDInstrument] [int] IDENTITY (1, 1) NOT NULL ,
[Instrument] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExchangeID] [int] NOT NULL ,
[GroupID] [int] NULL ,
[Collective] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED
INDEX [PK_tlkpInstruments] ON [dbo].[tlkpInstruments] ([IDInstrument])
WITH
DROP_EXISTING
ON [PRIMARY]
"TLD" wrote:
> The following query takes almost two minutes to run:
> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => Reference.dbo.tlkpInstruments.IDInstrument
> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> All rows retrieved in less than 5 seconds.
> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> All rows retrieved in less than 5 seconds.
> Why is the query running so slowly?
> Thanks.|||TLD, I don't see a thing wrong with your query from a performance
perspective.
Try this:
SELECT
i.IDInstrument,
i.Instrument,
a.*
into #bleh
FROM dbo.tblAccounts a
LEFT JOIN dbo.tlkpInstruments i
ON a.Commodities = i.IDInstrument;
DROP TABLE #bleh;
How long does that take? If it's quick, it probably indicates that you
have a problem with the speed of returning rows to the client. This can
be caused by a network configuration issue or by having some task on the
client side that's eating up CPU cycles.
What happens when you put this in front of your query? What are the
results in your "Messages" window?
SET STATISTICS IO ON;
-Dave
TLD wrote:
> Here are table and index definitions:
>
> CREATE TABLE [dbo].[tblAccounts] (
> [AccID] [bigint] NOT NULL ,
> [OffID] [int] NOT NULL ,
> [ID] [bigint] NULL ,
> [AccNo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AccName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [tinyint] NULL ,
> [3rdPartyGty] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Division] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [USRating] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UKRating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InitReqmnts] [int] NULL ,
> [StressExposure] [int] NULL ,
> [MarDefExcess] [int] NULL ,
> [ARTL] [int] NULL ,
> [SRTL] [int] NULL ,
> [IMTL] [int] NULL ,
> [PCOvr] [tinyint] NULL ,
> [Action] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EvalDate] [smalldatetime] NULL ,
> [RenewDate] [smalldatetime] NULL ,
> [LevelPrevious] [int] NULL ,
> [EvalDatePrevious] [smalldatetime] NULL ,
> [Note] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Exclude] [bit] NULL ,
> [NetLiquidity] [int] NULL ,
> [MaxRisk] [int] NULL ,
> [NotAmt] [int] NULL ,
> [MarReqd] [int] NULL ,
> [Country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHEquity] [int] NULL ,
> [EstProfit] [int] NULL ,
> [Exposure] [numeric](10, 2) NULL ,
> [Options] [bit] NULL ,
> [Contracts] [int] NULL ,
> [Symbol] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PriceCurrent] [money] NULL ,
> [PriceHistoric] [money] NULL ,
> [Documentation] [bit] NULL ,
> [Commodities] [int] NULL ,
> [Comment] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MD] [bit] NULL ,
> [Diversified] [smallint] NULL ,
> [Doc] [smallint] NULL ,
> [Credit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Legal] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accepted] [smallint] NULL ,
> [Man] [bit] NULL ,
> [TriggerContracts] [int] NULL ,
> [Inst] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> CREATE UNIQUE CLUSTERED
> INDEX [PK_tblAccounts] ON [dbo].[tblAccounts] ([AccID], [OffID],
> [Commodities])
> WITH
> FILLFACTOR = 90
> ,DROP_EXISTING
> ON [PRIMARY]
> CREATE TABLE [dbo].[tlkpInstruments] (
> [IDInstrument] [int] IDENTITY (1, 1) NOT NULL ,
> [Instrument] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExchangeID] [int] NOT NULL ,
> [GroupID] [int] NULL ,
> [Collective] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE UNIQUE CLUSTERED
> INDEX [PK_tlkpInstruments] ON [dbo].[tlkpInstruments] ([IDInstrument])
> WITH
> DROP_EXISTING
> ON [PRIMARY]
>
> "TLD" wrote:
>> The following query takes almost two minutes to run:
>> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>> FROM dbo.tblAccounts LEFT OUTER JOIN
>> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =>> Reference.dbo.tlkpInstruments.IDInstrument
>> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>> All rows retrieved in less than 5 seconds.
>> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>> All rows retrieved in less than 5 seconds.
>> Why is the query running so slowly?
>> Thanks.
-Dave Markle
http://www.markleconsulting.com/blog|||This is a local instance of SServer. There is no network connection. The only
client running is Query Analyzer.
The SELECT... INTO took one second.
Re-running the query takes 1m54s:
Application Profile Statistics
Timer resolution (milliseconds) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 1 1
Rows effected by SELECT statements 43348 43348
Number of user transactions 2 2
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0
Network Statistics
Number of server roundtrips 1 1
Number of TDS packets sent 1 1
Number of TDS packets received 2709 2709
Number of bytes sent 644 644
Number of bytes received 1.10936e+007 1.10936e+007
Time Statistics
Cumulative client processing time 0 0
Cumulative wait time on server replies 64 64
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
> TLD, I don't see a thing wrong with your query from a performance
> perspective.
> Try this:
> SELECT
> i.IDInstrument,
> i.Instrument,
> a.*
> into #bleh
> FROM dbo.tblAccounts a
> LEFT JOIN dbo.tlkpInstruments i
> ON a.Commodities = i.IDInstrument;
> DROP TABLE #bleh;
> How long does that take? If it's quick, it probably indicates that you
> have a problem with the speed of returning rows to the client. This can
> be caused by a network configuration issue or by having some task on the
> client side that's eating up CPU cycles.
> What happens when you put this in front of your query? What are the
> results in your "Messages" window?
> SET STATISTICS IO ON;
> -Dave
>
> TLD wrote:
> > Here are table and index definitions:
> >
> >
> > CREATE TABLE [dbo].[tblAccounts] (
> > [AccID] [bigint] NOT NULL ,
> > [OffID] [int] NOT NULL ,
> > [ID] [bigint] NULL ,
> > [AccNo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [AccName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Level] [tinyint] NULL ,
> > [3rdPartyGty] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Division] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [USRating] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [UKRating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InitReqmnts] [int] NULL ,
> > [StressExposure] [int] NULL ,
> > [MarDefExcess] [int] NULL ,
> > [ARTL] [int] NULL ,
> > [SRTL] [int] NULL ,
> > [IMTL] [int] NULL ,
> > [PCOvr] [tinyint] NULL ,
> > [Action] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [EvalDate] [smalldatetime] NULL ,
> > [RenewDate] [smalldatetime] NULL ,
> > [LevelPrevious] [int] NULL ,
> > [EvalDatePrevious] [smalldatetime] NULL ,
> > [Note] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Exclude] [bit] NULL ,
> > [NetLiquidity] [int] NULL ,
> > [MaxRisk] [int] NULL ,
> > [NotAmt] [int] NULL ,
> > [MarReqd] [int] NULL ,
> > [Country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [SHEquity] [int] NULL ,
> > [EstProfit] [int] NULL ,
> > [Exposure] [numeric](10, 2) NULL ,
> > [Options] [bit] NULL ,
> > [Contracts] [int] NULL ,
> > [Symbol] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PriceCurrent] [money] NULL ,
> > [PriceHistoric] [money] NULL ,
> > [Documentation] [bit] NULL ,
> > [Commodities] [int] NULL ,
> > [Comment] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [MD] [bit] NULL ,
> > [Diversified] [smallint] NULL ,
> > [Doc] [smallint] NULL ,
> > [Credit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Legal] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Accepted] [smallint] NULL ,
> > [Man] [bit] NULL ,
> > [TriggerContracts] [int] NULL ,
> > [Inst] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> >
> >
> > CREATE UNIQUE CLUSTERED
> > INDEX [PK_tblAccounts] ON [dbo].[tblAccounts] ([AccID], [OffID],
> > [Commodities])
> > WITH
> > FILLFACTOR = 90
> > ,DROP_EXISTING
> > ON [PRIMARY]
> >
> > CREATE TABLE [dbo].[tlkpInstruments] (
> > [IDInstrument] [int] IDENTITY (1, 1) NOT NULL ,
> > [Instrument] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ExchangeID] [int] NOT NULL ,
> > [GroupID] [int] NULL ,
> > [Collective] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> >
> > CREATE UNIQUE CLUSTERED
> > INDEX [PK_tlkpInstruments] ON [dbo].[tlkpInstruments] ([IDInstrument])
> > WITH
> > DROP_EXISTING
> > ON [PRIMARY]
> >
> >
> > "TLD" wrote:
> >
> >> The following query takes almost two minutes to run:
> >>
> >> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> >> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> >> FROM dbo.tblAccounts LEFT OUTER JOIN
> >> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => >> Reference.dbo.tlkpInstruments.IDInstrument
> >>
> >> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> >> All rows retrieved in less than 5 seconds.
> >>
> >> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> >> All rows retrieved in less than 5 seconds.
> >>
> >> Why is the query running so slowly?
> >>
> >> Thanks.
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
>|||The two minutes is slow, of course,
But your "5 seconds" timings are suspect, too.
Are you running on a tiny, tiny machine, or on a busy server with just
all kinds of other stuff going on?
You ought to be able to fetch both tables and the join in five seconds
easy, even with no keys at all, on anything like a current machine.
J.
On Fri, 26 Jan 2007 09:38:01 -0800, TLD
<TLD@.discussions.microsoft.com> wrote:
>The following query takes almost two minutes to run:
>SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =>Reference.dbo.tlkpInstruments.IDInstrument
>tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>All rows retrieved in less than 5 seconds.
>tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>All rows retrieved in less than 5 seconds.
>Why is the query running so slowly?
>Thanks.|||I'm using a dual-Opteron 64 workstation with 2G of memory. SQL Server is
installed as a local instance. There is no active network connection.
"JXStern" wrote:
> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
> >The following query takes almost two minutes to run:
> >
> >SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> >Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> >FROM dbo.tblAccounts LEFT OUTER JOIN
> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => >Reference.dbo.tlkpInstruments.IDInstrument
> >
> >tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> >All rows retrieved in less than 5 seconds.
> >
> >tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> >All rows retrieved in less than 5 seconds.
> >
> >Why is the query running so slowly?
> >
> >Thanks.
>|||I should add that the performance is the same when running with an Access
front-end in a server environment. This is why I'm trying to get it
straightened out on a local machine first. The speed of return of the tables
by themselves is less than five seconds, more like three.
This shouldn't make any difference but there are many more records in the
main table without an associated record in the subsidiary than there are with
one. That brings up a lot of NULLs.
What about the order of the fields in the join? Could that make any
difference?
"JXStern" wrote:
> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
> >The following query takes almost two minutes to run:
> >
> >SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> >Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> >FROM dbo.tblAccounts LEFT OUTER JOIN
> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => >Reference.dbo.tlkpInstruments.IDInstrument
> >
> >tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> >All rows retrieved in less than 5 seconds.
> >
> >tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> >All rows retrieved in less than 5 seconds.
> >
> >Why is the query running so slowly?
> >
> >Thanks.
>|||Here is an answer:
The field which is causing the delay is
TriggerContracts int 4
When all except this field are accessed with the join, the return is less
than five seconds. When this field is included the return is two minutes.
Bracketing the name doesn't change the performance. Changing the name to
TContracts doesn't change the performance. Add the field to the clustered
index doesn't change the performance. What in the world is going on?
"JXStern" wrote:
> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
> >The following query takes almost two minutes to run:
> >
> >SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> >Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> >FROM dbo.tblAccounts LEFT OUTER JOIN
> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => >Reference.dbo.tlkpInstruments.IDInstrument
> >
> >tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> >All rows retrieved in less than 5 seconds.
> >
> >tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> >All rows retrieved in less than 5 seconds.
> >
> >Why is the query running so slowly?
> >
> >Thanks.
>|||The problem has been solved. By reworking my front end, I was able to
eliminate some of the fields in the query. Now it runs in less than five
seconds.
Thanks to all for your help.
"JXStern" wrote:
> The two minutes is slow, of course,
> But your "5 seconds" timings are suspect, too.
> Are you running on a tiny, tiny machine, or on a busy server with just
> all kinds of other stuff going on?
> You ought to be able to fetch both tables and the join in five seconds
> easy, even with no keys at all, on anything like a current machine.
> J.
>
> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
> >The following query takes almost two minutes to run:
> >
> >SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> >Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> >FROM dbo.tblAccounts LEFT OUTER JOIN
> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => >Reference.dbo.tlkpInstruments.IDInstrument
> >
> >tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> >All rows retrieved in less than 5 seconds.
> >
> >tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> >All rows retrieved in less than 5 seconds.
> >
> >Why is the query running so slowly?
> >
> >Thanks.
>|||On Sun, 28 Jan 2007 08:46:00 -0800, TLD
<TLD@.discussions.microsoft.com> wrote:
>The problem has been solved. By reworking my front end, I was able to
>eliminate some of the fields in the query. Now it runs in less than five
>seconds.
Aha. Was that five seconds / two minutes to your app, or were those
timings from the query analyzer? I've seen situations where the front
end was busy and didn't accept the results for thirty seconds or more,
especially if all development is being done on a single system, was
that your situation?
Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
an open instance of VS, might be running short of memory - VS being an
unbelievable pig. Might run a lot faster on even a small server over
the network.
J.
>Thanks to all for your help.
>"JXStern" wrote:
>> The two minutes is slow, of course,
>> But your "5 seconds" timings are suspect, too.
>> Are you running on a tiny, tiny machine, or on a busy server with just
>> all kinds of other stuff going on?
>> You ought to be able to fetch both tables and the join in five seconds
>> easy, even with no keys at all, on anything like a current machine.
>> J.
>>
>> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
>> <TLD@.discussions.microsoft.com> wrote:
>> >The following query takes almost two minutes to run:
>> >
>> >SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>> >Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>> >FROM dbo.tblAccounts LEFT OUTER JOIN
>> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =>> >Reference.dbo.tlkpInstruments.IDInstrument
>> >
>> >tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>> >All rows retrieved in less than 5 seconds.
>> >
>> >tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>> >All rows retrieved in less than 5 seconds.
>> >
>> >Why is the query running so slowly?
>> >
>> >Thanks.
>>|||That was from Query Analyzer (five seconds) on the local system. I also
tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
disk) with 1G of memory. There it runs in just under 30 seconds, which is
equivalent to the opening of the main form.
Thanks for your help.
"JXStern" wrote:
> On Sun, 28 Jan 2007 08:46:00 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
> >The problem has been solved. By reworking my front end, I was able to
> >eliminate some of the fields in the query. Now it runs in less than five
> >seconds.
> Aha. Was that five seconds / two minutes to your app, or were those
> timings from the query analyzer? I've seen situations where the front
> end was busy and didn't accept the results for thirty seconds or more,
> especially if all development is being done on a single system, was
> that your situation?
> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
> an open instance of VS, might be running short of memory - VS being an
> unbelievable pig. Might run a lot faster on even a small server over
> the network.
> J.
>
>
> >
> >Thanks to all for your help.
> >
> >"JXStern" wrote:
> >
> >> The two minutes is slow, of course,
> >>
> >> But your "5 seconds" timings are suspect, too.
> >>
> >> Are you running on a tiny, tiny machine, or on a busy server with just
> >> all kinds of other stuff going on?
> >>
> >> You ought to be able to fetch both tables and the join in five seconds
> >> easy, even with no keys at all, on anything like a current machine.
> >>
> >> J.
> >>
> >>
> >> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> >> <TLD@.discussions.microsoft.com> wrote:
> >>
> >> >The following query takes almost two minutes to run:
> >> >
> >> >SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> >> >Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> >> >FROM dbo.tblAccounts LEFT OUTER JOIN
> >> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => >> >Reference.dbo.tlkpInstruments.IDInstrument
> >> >
> >> >tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> >> >All rows retrieved in less than 5 seconds.
> >> >
> >> >tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> >> >All rows retrieved in less than 5 seconds.
> >> >
> >> >Why is the query running so slowly?
> >> >
> >> >Thanks.
> >>
> >>
>|||On Sun, 28 Jan 2007 10:28:01 -0800, TLD
<TLD@.discussions.microsoft.com> wrote:
>That was from Query Analyzer (five seconds) on the local system. I also
>tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
>disk) with 1G of memory. There it runs in just under 30 seconds, which is
>equivalent to the opening of the main form.
>Thanks for your help.
So, was VS open on the "server"?
If I helped, you're welcome,
but I guess I'm still mystified.
J.
>
>"JXStern" wrote:
>> On Sun, 28 Jan 2007 08:46:00 -0800, TLD
>> <TLD@.discussions.microsoft.com> wrote:
>> >The problem has been solved. By reworking my front end, I was able to
>> >eliminate some of the fields in the query. Now it runs in less than five
>> >seconds.
>> Aha. Was that five seconds / two minutes to your app, or were those
>> timings from the query analyzer? I've seen situations where the front
>> end was busy and didn't accept the results for thirty seconds or more,
>> especially if all development is being done on a single system, was
>> that your situation?
>> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
>> an open instance of VS, might be running short of memory - VS being an
>> unbelievable pig. Might run a lot faster on even a small server over
>> the network.
>> J.
>>
>>
>> >
>> >Thanks to all for your help.
>> >
>> >"JXStern" wrote:
>> >
>> >> The two minutes is slow, of course,
>> >>
>> >> But your "5 seconds" timings are suspect, too.
>> >>
>> >> Are you running on a tiny, tiny machine, or on a busy server with just
>> >> all kinds of other stuff going on?
>> >>
>> >> You ought to be able to fetch both tables and the join in five seconds
>> >> easy, even with no keys at all, on anything like a current machine.
>> >>
>> >> J.
>> >>
>> >>
>> >> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
>> >> <TLD@.discussions.microsoft.com> wrote:
>> >>
>> >> >The following query takes almost two minutes to run:
>> >> >
>> >> >SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>> >> >Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>> >> >FROM dbo.tblAccounts LEFT OUTER JOIN
>> >> > Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =>> >> >Reference.dbo.tlkpInstruments.IDInstrument
>> >> >
>> >> >tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>> >> >All rows retrieved in less than 5 seconds.
>> >> >
>> >> >tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>> >> >All rows retrieved in less than 5 seconds.
>> >> >
>> >> >Why is the query running so slowly?
>> >> >
>> >> >Thanks.
>> >>
>> >>
>>|||It's still shocking that these queries take more than a second to execute.
JXStern wrote:
> On Sun, 28 Jan 2007 10:28:01 -0800, TLD
> <TLD@.discussions.microsoft.com> wrote:
>> That was from Query Analyzer (five seconds) on the local system. I also
>> tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
>> disk) with 1G of memory. There it runs in just under 30 seconds, which is
>> equivalent to the opening of the main form.
>> Thanks for your help.
> So, was VS open on the "server"?
> If I helped, you're welcome,
> but I guess I'm still mystified.
> J.
>
>> "JXStern" wrote:
>> On Sun, 28 Jan 2007 08:46:00 -0800, TLD
>> <TLD@.discussions.microsoft.com> wrote:
>> The problem has been solved. By reworking my front end, I was able to
>> eliminate some of the fields in the query. Now it runs in less than five
>> seconds.
>> Aha. Was that five seconds / two minutes to your app, or were those
>> timings from the query analyzer? I've seen situations where the front
>> end was busy and didn't accept the results for thirty seconds or more,
>> especially if all development is being done on a single system, was
>> that your situation?
>> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
>> an open instance of VS, might be running short of memory - VS being an
>> unbelievable pig. Might run a lot faster on even a small server over
>> the network.
>> J.
>>
>>
>> Thanks to all for your help.
>> "JXStern" wrote:
>> The two minutes is slow, of course,
>> But your "5 seconds" timings are suspect, too.
>> Are you running on a tiny, tiny machine, or on a busy server with just
>> all kinds of other stuff going on?
>> You ought to be able to fetch both tables and the join in five seconds
>> easy, even with no keys at all, on anything like a current machine.
>> J.
>>
>> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
>> <TLD@.discussions.microsoft.com> wrote:
>> The following query takes almost two minutes to run:
>> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>> >FROM dbo.tblAccounts LEFT OUTER JOIN
>> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =>> Reference.dbo.tlkpInstruments.IDInstrument
>> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>> All rows retrieved in less than 5 seconds.
>> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
>> All rows retrieved in less than 5 seconds.
>> Why is the query running so slowly?
>> Thanks.
>>
>
-Dave Markle
http://www.markleconsulting.com/blog|||You're not the only one who's mystified.
The summary is this:
When the query had the field TriggerContracts plus those following, it took
1m55s to run locally on workstation, 2m20s to run on networked laptop. When
the number of fields was reduced, it took 5s to run locally on workstation,
30s to run on networked laptop. It didn't matter which fields were there; it
was purely the number of them.
What I've learned from all of this is that, to test a join you should use
only one field from each table.
Thanks again,
Tom
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
> It's still shocking that these queries take more than a second to execute.
> JXStern wrote:
> > On Sun, 28 Jan 2007 10:28:01 -0800, TLD
> > <TLD@.discussions.microsoft.com> wrote:
> >
> >> That was from Query Analyzer (five seconds) on the local system. I also
> >> tested on a network with the Access front end on a 2G Thinkpad (5400 rpm
> >> disk) with 1G of memory. There it runs in just under 30 seconds, which is
> >> equivalent to the opening of the main form.
> >>
> >> Thanks for your help.
> >
> > So, was VS open on the "server"?
> >
> > If I helped, you're welcome,
> > but I guess I'm still mystified.
> >
> > J.
> >
> >
> >
> >>
> >> "JXStern" wrote:
> >>
> >> On Sun, 28 Jan 2007 08:46:00 -0800, TLD
> >> <TLD@.discussions.microsoft.com> wrote:
> >>
> >> The problem has been solved. By reworking my front end, I was able to
> >> eliminate some of the fields in the query. Now it runs in less than five
> >> seconds.
> >> Aha. Was that five seconds / two minutes to your app, or were those
> >> timings from the query analyzer? I've seen situations where the front
> >> end was busy and didn't accept the results for thirty seconds or more,
> >> especially if all development is being done on a single system, was
> >> that your situation?
> >>
> >> Not to mention, a dual-64 with (only!) 2gb RAM, running SQLServer and
> >> an open instance of VS, might be running short of memory - VS being an
> >> unbelievable pig. Might run a lot faster on even a small server over
> >> the network.
> >>
> >> J.
> >>
> >>
> >>
> >>
> >> Thanks to all for your help.
> >>
> >> "JXStern" wrote:
> >>
> >> The two minutes is slow, of course,
> >>
> >> But your "5 seconds" timings are suspect, too.
> >>
> >> Are you running on a tiny, tiny machine, or on a busy server with just
> >> all kinds of other stuff going on?
> >>
> >> You ought to be able to fetch both tables and the join in five seconds
> >> easy, even with no keys at all, on anything like a current machine.
> >>
> >> J.
> >>
> >>
> >> On Fri, 26 Jan 2007 09:38:01 -0800, TLD
> >> <TLD@.discussions.microsoft.com> wrote:
> >>
> >> The following query takes almost two minutes to run:
> >>
> >> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> >> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> >> >FROM dbo.tblAccounts LEFT OUTER JOIN
> >> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities => >> Reference.dbo.tlkpInstruments.IDInstrument
> >>
> >> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> >> All rows retrieved in less than 5 seconds.
> >>
> >> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int).
> >> All rows retrieved in less than 5 seconds.
> >>
> >> Why is the query running so slowly?
> >>
> >> Thanks.
> >>
> >>
> >
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
>

Performance of query joining tables linked between different datab

The following query takes almost two minutes to run:
SELECT Reference.dbo.tlkpInstruments.IDInstrument,
Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
FROM dbo.tblAccounts LEFT OUTER JOIN
Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
Reference.dbo.tlkpInstruments.IDInstrument
tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
All rows retrieved in less than 5 seconds.
tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int)
.
All rows retrieved in less than 5 seconds.
Why is the query running so slowly?
Thanks.Start by adding indexes on the columns that you do the join over. Also consi
der making one or even
both of the indexes clustered indexes (assuming that is suitable).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:5FF860E0-152C-4C5C-AB90-1FA78D9E05B1@.microsoft.com...
> The following query takes almost two minutes to run:
> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
> Reference.dbo.tlkpInstruments.IDInstrument
> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> All rows retrieved in less than 5 seconds.
> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (in
t).
> All rows retrieved in less than 5 seconds.
> Why is the query running so slowly?
> Thanks.|||Here are table and index definitions:
CREATE TABLE [dbo].[tblAccounts] (
[AccID] [bigint] NOT NULL ,
[OffID] [int] NOT NULL ,
[ID] [bigint] NULL ,
[AccNo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [tinyint] NULL ,
[3rdPartyGty] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Division] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[USRating] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKRating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InitReqmnts] [int] NULL ,
[StressExposure] [int] NULL ,
[MarDefExcess] [int] NULL ,
[ARTL] [int] NULL ,
[SRTL] [int] NULL ,
[IMTL] [int] NULL ,
[PCOvr] [tinyint] NULL ,
[Action] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EvalDate] [smalldatetime] NULL ,
[RenewDate] [smalldatetime] NULL ,
[LevelPrevious] [int] NULL ,
[EvalDatePrevious] [smalldatetime] NULL ,
[Note] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exclude] [bit] NULL ,
[NetLiquidity] [int] NULL ,
[MaxRisk] [int] NULL ,
[NotAmt] [int] NULL ,
[MarReqd] [int] NULL ,
[Country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHEquity] [int] NULL ,
[EstProfit] [int] NULL ,
[Exposure] [numeric](10, 2) NULL ,
[Options] [bit] NULL ,
[Contracts] [int] NULL ,
[Symbol] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PriceCurrent] [money] NULL ,
[PriceHistoric] [money] NULL ,
[Documentation] [bit] NULL ,
[Commodities] [int] NULL ,
[Comment] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MD] [bit] NULL ,
[Diversified] [smallint] NULL ,
[Doc] [smallint] NULL ,
[Credit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Legal] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accepted] [smallint] NULL ,
[Man] [bit] NULL ,
[TriggerContracts] [int] NULL ,
[Inst] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED
INDEX [PK_tblAccounts] ON [dbo].[tblAccounts] ([AccID], [
;OffID],
[Commodities])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
CREATE TABLE [dbo].[tlkpInstruments] (
[IDInstrument] [int] IDENTITY (1, 1) NOT NULL ,
[Instrument] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExchangeID] [int] NOT NULL ,
[GroupID] [int] NULL ,
[Collective] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED
INDEX [PK_tlkpInstruments] ON [dbo].[tlkpInstruments] ([IDIn
strument])
WITH
DROP_EXISTING
ON [PRIMARY]
"TLD" wrote:

> The following query takes almost two minutes to run:
> SELECT Reference.dbo.tlkpInstruments.IDInstrument,
> Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
> FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
> Reference.dbo.tlkpInstruments.IDInstrument
> tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
> All rows retrieved in less than 5 seconds.
> tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (in
t).
> All rows retrieved in less than 5 seconds.
> Why is the query running so slowly?
> Thanks.|||The two minutes is slow, of course,
But your "5 seconds" timings are suspect, too.
Are you running on a tiny, tiny machine, or on a busy server with just
all kinds of other stuff going on?
You ought to be able to fetch both tables and the join in five seconds
easy, even with no keys at all, on anything like a current machine.
J.
On Fri, 26 Jan 2007 09:38:01 -0800, TLD
<TLD@.discussions.microsoft.com> wrote:

>The following query takes almost two minutes to run:
>SELECT Reference.dbo.tlkpInstruments.IDInstrument,
>Reference.dbo.tlkpInstruments.Instrument, dbo.tblAccounts.*
>FROM dbo.tblAccounts LEFT OUTER JOIN
> Reference.dbo.tlkpInstruments ON dbo.tblAccounts.Commodities =
>Reference.dbo.tlkpInstruments.IDInstrument
>tlkpInstruments contains 4140 rows. Primary key is IDInstrument (int).
>All rows retrieved in less than 5 seconds.
>tblAccounts contains 43348 rows. Primary key is AccID (bigint) + OffID (int
).
>All rows retrieved in less than 5 seconds.
>Why is the query running so slowly?
>Thanks.