Optimising Query - SQL Server Forums

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

Transact-SQL (2005)

Optimising Query

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-03-16 : 06:42:09

Hi there

I have stored procedure


ALTER PROCEDURE [dbo].[prf_GetBatchItemFieldList]
(
	@FieldName VARCHAR(128),
	@BatchList VARCHAR(512)
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Sql VARCHAR(4000)

	SET @Sql = 'SELECT DISTINCT ' + @FieldName + ' ' +
				'FROM prf_batchitems (NOLOCK) ' +
				'WHERE BatchID IN (' + @BatchList + ')'
	PRINT @Sql	
	EXEC(@Sql)

END

and running as follow: EXEC dbo.prf_GetBatchItemFieldList 'AccountNo', '25'

The issue is that this table batchitems has 15+ million records and it's growing rapidly. How do I optimise this?

So far it took me 2 minute and 7 seconds on average to get 6 records.

I am thinking to create a VIEW of:
SELECT DISTINCT AccountNo, BatchID
FROM dbo.prf_BatchItems WITH (NOLOCK)

But it turns out not much different different? Any ideas?

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 07:20:24

Do you have proper indexes on table ? You might want to check for fragmentation level and parameter sniffing also.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-16 : 08:22:15

Do you have an index on BatchID? And do you notice any performance difference when running the query without using dynamic sql?

- Lumbago

dewacorp.alliances

452 Posts

Posted - 2009-03-16 : 16:00:41

quote:
Originally posted by Lumbago

Do you have an index on BatchID? And do you notice any performance difference when running the query without using dynamic sql?

- Lumbago

Ah yes ... I missed this BatchID. Thanks for this.

dewacorp.alliances

452 Posts

Posted - 2009-03-16 : 16:01:37

quote:
Originally posted by sakets_2000

Do you have proper indexes on table ? You might want to check for fragmentation level and parameter sniffing also.

What do you mean by fragmentation level? Could you explain this a bit further?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:36:23

These links explain it in detail.
http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx
http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources