Slow query on million records table

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

Transact-SQL (2008)

Slow query on million records table

Author

Topic

chorofonfilo
Starting Member

40 Posts

Posted - 2012-08-05 : 01:41:06

Hello friends and thanks for your help in advance.

I am performing a query that performs a join between two tables:

SELECT
F5_CNOMBRE,
F5_CTD,
F5_CNUMSER,
F5_CNUMDOC,
F5_DFECDOC,
F5_NIMPORT,
CD_DFECCAN,
CD_CCODART
FROM ft1
INNER JOIN cc1
ON RTRIM(cc1.CD_CNRODOC) = RTRIM(ft1.F5_CNUMSER) + ft1.F5_CNUMDOC
WHERE F5_CGLOSA LIKE @varNroExpediente + '%'

The table ft1 has around 1 millions of records and the table cc1 has around 700 000.

The query runs fast when no one is writing on those tables, but when users are using them the query takes around 30/50 seconds to finish.

I was thinking on creating an indexed view, indexing the fields ft1.F5_CNUMSER and ft1.F5_CNUMDOC. I think this would improve the performance. I wanted your opinion on the subject also, if I create the index(s) on the view is there any chance the queried tables could be damaged on the operation, those are very important tables for the actual system.

I hope you guys can give me some insight on the subject.

Thank you!

Perseverance worths it...:)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-05 : 07:28:51

First make sure that its just a blocking, which is causing slowness of query. Use nolock to compare performance
SELECT
F5_CNOMBRE,
F5_CTD,
F5_CNUMSER,
F5_CNUMDOC,
F5_DFECDOC,
F5_NIMPORT,
CD_DFECCAN,
CD_CCODART
FROM ft1 WITH (NOLOCK)
INNER JOIN cc1 WITH (NOLOCK)
ON RTRIM(cc1.CD_CNRODOC) = RTRIM(ft1.F5_CNUMSER) + ft1.F5_CNUMDOC
WHERE F5_CGLOSA LIKE @varNroExpediente + '%'

NOLOCK hint can force to dirty read, and possibly not fit for your environment. If so you can use Snapshot isolation level

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT
F5_CNOMBRE,
F5_CTD,
F5_CNUMSER,
F5_CNUMDOC,
F5_DFECDOC,
F5_NIMPORT,
CD_DFECCAN,
CD_CCODART
FROM ft1
INNER JOIN cc1
ON RTRIM(cc1.CD_CNRODOC) = RTRIM(ft1.F5_CNUMSER) + ft1.F5_CNUMDOC
WHERE F5_CGLOSA LIKE @varNroExpediente + '%'

--------------------------
http://connectsql.blogspot.com/

chorofonfilo
Starting Member

40 Posts

Posted - 2012-08-05 : 10:33:41

quote:
Originally posted by lionofdezert

First make sure that its just a blocking, which is causing slowness of query. Use nolock to compare performance
SELECT
F5_CNOMBRE,
F5_CTD,
F5_CNUMSER,
F5_CNUMDOC,
F5_DFECDOC,
F5_NIMPORT,
CD_DFECCAN,
CD_CCODART
FROM ft1 WITH (NOLOCK)
INNER JOIN cc1 WITH (NOLOCK)
ON RTRIM(cc1.CD_CNRODOC) = RTRIM(ft1.F5_CNUMSER) + ft1.F5_CNUMDOC
WHERE F5_CGLOSA LIKE @varNroExpediente + '%'

NOLOCK hint can force to dirty read, and possibly not fit for your environment. If so you can use Snapshot isolation level

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT
F5_CNOMBRE,
F5_CTD,
F5_CNUMSER,
F5_CNUMDOC,
F5_DFECDOC,
F5_NIMPORT,
CD_DFECCAN,
CD_CCODART
FROM ft1
INNER JOIN cc1
ON RTRIM(cc1.CD_CNRODOC) = RTRIM(ft1.F5_CNUMSER) + ft1.F5_CNUMDOC
WHERE F5_CGLOSA LIKE @varNroExpediente + '%'

--------------------------
http://connectsql.blogspot.com/

Hi lion.

How do I apply the NOLOCK on the first statement?. Should it go in the stored procedure that contains the query?. Would it affect the table or just the select?. I want to be sure that I am applying something that is safe.

Thank you for your time.

Perseverance worths it...:)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-05 : 11:05:45

NOLOCK is a table hint, provide it in front of your table name in query, like i did it over here.

First make sure that its just a blocking, which is causing slowness of query. Use nolock to compare performance
SELECT
F5_CNOMBRE,
F5_CTD,
F5_CNUMSER,
F5_CNUMDOC,
F5_DFECDOC,
F5_NIMPORT,
CD_DFECCAN,
CD_CCODART
FROM ft1 WITH (NOLOCK)
INNER JOIN cc1 WITH (NOLOCK)
ON RTRIM(cc1.CD_CNRODOC) = RTRIM(ft1.F5_CNUMSER) + ft1.F5_CNUMDOC
WHERE F5_CGLOSA LIKE @varNroExpediente + '%'

Its not gonna harm your data. However, select query can show you updated values, which are NOT still committed by other transactions.

--------------------------
http://connectsql.blogspot.com/

chorofonfilo
Starting Member

40 Posts

Posted - 2012-08-05 : 11:55:37

I will try and let you know. Thank you lion.

Perseverance worths it...:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources