count is very slow(3000000 rows)

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)

count is very slow(3000000 rows)

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2011-10-04 : 02:26:42

i have a g_service table,has 3000000 rows,i execute this command but executed time very long,this table hasn't any index or stored procedure,what will i do and how?
please help me?

SELECT
COUNT(SERVICE.SERVICE_ID) as aaa
FROM
calisma.dbo.G_SERVICE SERVICE WITH (NOLOCK),
G_SERVICE_APPCAT SERVICE_APPCAT,
SETUP_PRIORITY SP,
PROCESS_TYPE_ROWS PROCESS_TYPE_ROWS
WHERE
SERVICE.SERVICECAT_ID = SERVICE_APPCAT.SERVICECAT_ID AND
SP.PRIORITY_ID = SERVICE.PRIORITY_ID AND
SERVICE.SERVICE_STATUS_ID = PROCESS_TYPE_ROWS.PROCESS_ROW_ID AND
(
SERVICE.SERVICE_DETAIL LIKE '%sbn%' OR
SERVICE.SERVICE_HEAD LIKE '%sbn%' OR
SERVICE.SERVICE_NO LIKE '%sbn%'

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 02:28:47

you're using like and wildcard search so it wont use an index even if present on those fields. you can try adding index on other fields in where.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2011-10-04 : 02:42:35

so i will create index only on SERVICE.SERVICECAT_ID AND SERVICE.PRIORITY_ID AND
SERVICE.SERVICE_STATUS_ID and service_id. this is true?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 03:00:11

view the execution plan and it will show you recommendation for indexes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-04 : 22:58:27

"OR" can cause huge delays, try this where clause

SERVICE.SERVICECAT_ID = SERVICE_APPCAT.SERVICECAT_ID AND
SP.PRIORITY_ID = SERVICE.PRIORITY_ID AND
SERVICE.SERVICE_STATUS_ID = PROCESS_TYPE_ROWS.PROCESS_ROW_ID AND
SERVICE.SERVICE_DETAIL +'-'+ SERVICE.SERVICE_HEAD +'-'+SERVICE.SERVICE_NO LIKE '%sbn%'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources