need ORDER BY performance help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need ORDER BY performance help

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-12-13 : 12:51:53

I have a stored procedure that grabs anywhere from 0 to 300,000 rows depending on the parameters passed to it. The primary table the returned data comes from holds transactions, with mostly integer and datetime columns. Executing the stored procedure generally takes around 3 seconds, but if I take out "ORDER BY date_submitted" it takes less than 1 second. The Estimated Execution Plan shows the Order By taking up anywhere from 30-55%, depending on the server I look at. If I place the results of the Select into a temp table and put the Order By on the temp table, the stored procedure is about as fast as having no Order By, but this seems crude. I've tried indexing the date_submitted column and I've tried updating statistics on the table, but neither helped, and I don't want to slow down inserts anyway. Does this seem strange to anybody? Any other ideas?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 13:42:06

We'd need to see an example of your table structure and indexes and your SQL statement to help. I am not sure if there are any general tips that we can provide, since so much depends on the specifics.

- Jeff
http://weblogs.sqlteam.com/JeffS

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-12-13 : 13:47:35

CREATE TABLE [t_trans_transaction] (
[transaction_id] [int] IDENTITY (1, 1) NOT NULL ,
[person_id] [int] NOT NULL ,
[ei_id] [int] NOT NULL ,
[address_id] [int] NOT NULL ,
[plan_id] [int] NOT NULL ,
[action_id] [int] NOT NULL ,
[contribution_id] [int] NULL ,
[distribution_id] [int] NULL ,
[created_by_admin] [bit] NOT NULL CONSTRAINT [DF_t_trans_transaction_created_by_admin_1] DEFAULT (0),
[admin_user_name] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status_id] [int] NOT NULL ,
[date_submitted] [datetime] NOT NULL ,
[date_signed] [datetime] NOT NULL CONSTRAINT [DF__t_trans_t__date___51DA19CB] DEFAULT (getdate()),
[reenrollment_id] [int] NULL ,
[estimate_id] [bigint] NULL ,
[note_id] [int] NULL ,
[eext_id] [int] NULL ,
CONSTRAINT [PK_t_trans_transaction_1] PRIMARY KEY CLUSTERED
(
[transaction_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_trans_transaction_t_trans_lk_status] FOREIGN KEY
(
[status_id]
) REFERENCES [t_trans_lk_status] (
[status_id]
)
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[sp_trans_get_transactions]

(
@person_id int = null,
@plan_id int = null,
@action_id int = null,
@status_id int = null
)
AS
SET NOCOUNT ON

select
'transaction_id' = t.transaction_id,
'action_id' = t.action_id,
'plan_id' = t.plan_id,
'plan_name' = plans.plan_description,
'date_submitted' = t.date_submitted,
'status_id' = t.status_id,
'status_name' = s.[name]
from
dbo.t_trans_transaction t
join
dbo.t_trans_lk_actions actions
on
actions.action_id = t.action_id
left join
dbo.t_lk_plans plans
on
plans.plan_id = t.plan_id
join
dbo.t_users u
on
u.person_id = t.person_id
left join
dbo.t_required_action_history ah1
on
t.transaction_id = ah1.transaction_id
and
ah1.activity_group_id = 1
left join
dbo.t_required_action_history ah2
on
t.transaction_id = ah2.transaction_id
and
ah2.activity_group_id = 2
join
dbo.t_trans_lk_actions a
on
a.action_id = t.action_id
join
dbo.t_trans_lk_status_new s
on
s.status_id = t.status_id
where
(
@plan_id is null
or
@plan_id = t.plan_id
)

AND
(
@action_id is null
or
@action_id = t.action_id
)
and
(
@status_id is null
or
@status_id = t.status_id
)
and
(
@person_id is null
or
@person_id = t.person_id
)
order by
date_submitted desc
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 13:52:40

Did you say Date_Submitted is indexed?

- Jeff
http://weblogs.sqlteam.com/JeffS

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-12-13 : 13:57:51

I tried indexing it on a test server but that didn't help...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 14:13:42

Indexing will always help. The cost slowing inserts or updates is negligible; I have never seen it not worth it to add an index. Are you sure your test environment is exactly the same as your production? And that you analyzed equivalent sql statements and you didn't measure the time taken to return the results to the client (i.e., query analyzer or an application or report) ? How did you compare the performance before the index and after the index, what steps did you take and how did you time it using what apps?

Any way you slice it, it will take LONG time to return 300,000 results, no matter how fast or efficient your SQL server is, due to network speed, memory and disk speed of the client, and other issues. 3 seconds to return 300,000 rows sounds reasonable to me. the bottleneck might be returning all that data, not processing a SQL statement at the server, which is why indexing doesn't seem to help.

Anyway, I don't see anything unusual about any of this.

- Jeff
http://weblogs.sqlteam.com/JeffS

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-12-13 : 14:27:44

I forgot to mention that almost all the time the procedure is executed it returns fewer than 10 rows (we usually pass in person_id and status_id) and it still takes 3 seconds those times. How do I know what portion of the execution time was from processing versus network transfer?

BTW, I disagree about indexing always helping, from my experience it can do very little or slow down inserts greatly (which may be a db design problem in the cases I've seen it).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 14:46:46

When you added an index to date_submitted, did you specify its order to be desc just like your query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-12-13 : 14:51:42

Nope, I'll give that a shot.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-12-13 : 16:35:36

Changing the index to DESC doesn't seem to make more than a tiny difference. Any other thoughts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 16:39:40

Could you provide the actual execution plan for your query? An image is preferable.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources