| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2012-01-29 : 04:42:07
|
| i'm doing a querySelect p.participantid, p.lastname, p.wifesname,p.firstname, p.cityid, c.city, s.state, ctry.country, p.stateid, p.countryid, p.phonenumber1, p.wifescellphone, p.wifesemail, w.wnameoforganization, w.wposition, l.positiontype From participants as p Left join wplacements as w on p.participantid=w.participantid Left join city as c on p.cityid=c.id Left join state as s on p.stateid=s.id Left join country as ctry on p.countryid=ctry.id Left join placements as l on p.participantid=l.participantidad it's taking 11 seconds to run - why would this be taking so long to run? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2012-01-29 : 06:18:38
|
| That is a very open ended question, could be any number of things. The first place I would start though, is the execution plan. Take a look for spots where you're getting a clustered index scan or a table scan to start with. In the plan you'll be able to see where the highest percentage is and start to whittle away at it. Mike"oh, that monkey is going to pay" |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-01-29 : 06:40:18
|
| Do an 1) UPDATE STATISTICS with FULL SCAN and a DBCC UPDATEUSAGE , before you run the query.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-29 : 07:23:00
|
| DBCC UpdateUsage won't affect performance. It's solely to correct inaccurate page usage metadata that can lead to incorrect values for space available and used in the DB.--Gail ShawSQL Server MVP |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-01-29 : 07:33:15
|
| so what should I check for as far as indexesI created an index for cityid , countryid ,stateid , participantid in the 2 tables |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-29 : 08:42:52
|
| Honestly, I don't think any indexes will help much.You're running an unrestricted join of 6 tables, so SQL has to read all of every table anyway (table scan), there's nothing that it can seek on with that query, no predicates that can reduce the resultset.How many rows does that return?--Gail ShawSQL Server MVP |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-01-29 : 08:56:49
|
| so is there any other way to do it with the joins?what's the recommended way to do thisthere are 4000 records in the table but it seems to take a long time even with a where clause |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-29 : 11:16:59
|
| First question would be why you need all 4000 rows?If 4000 rows is taking 11 sec, then there's probably something slow in the hardware, IO speed, memory capacity or network speed, because even a 6-table join with a few thousand rows should take a second, maybe 2.--Gail ShawSQL Server MVP |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-01-29 : 11:39:18
|
| I don't need all 4000usually this will end with a where clause at the endthis is on a shared hosting platform - you think there is an issue there? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-29 : 11:42:04
|
| If it's got a where clause, then please post the version with the where clause. Asking for optimisation help and posting half the query is not going to get you any useful help is and is just going to waste your time and that of anyone attempting to help.--Gail ShawSQL Server MVP |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-29 : 14:27:42
|
| first you need to identify what columns you will be using in where clause and also selectivity of data in them. Based on that factor, you can come u with index covering all columns which are part of your where clause------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-02-04 : 17:31:00
|
| 1)SQL DDL , meaning Data Definition Language. Normally , this is a CREATE TABLE script .2)Data sample . In a format easy to import , this could be a CSV or an INSERT statementJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
query taking over 11 seconds to run