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.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2008-03-02 : 09:01:58
|
| I have a query that is taking 6 mintues on my db so i copied the 2 relevant tables to a new db for testing and then tested the query and it took only 30 seconds.why would this be? tables are the same size - i copied to the new by creating the table and then inserting from the old to then new.these are very big tables.the first db does have many other tables but why should that make a difference? |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-02 : 09:43:30
|
how many records?What type and how many indexes exist on the original?Are both DB's on the same physical drive?Were there potentially other queries hitting the original db that used resources? were updates occurring?What is the query?check the fragmentation level of the old DB (DBCC SHOWCONTIG)
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-03-02 : 11:11:05
|
| millions of records in one of the tableboth db's are on teh same physical drive?query is just an inner join between the 2 with a where clause.how do i check fragmentation and what else is there to check? anyway to compact and repair as you would do in access to get rid of extra space? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-02 : 12:00:42
|
Millions of records is not a big deal for SQL.Are the joining columns indexed? the column in the Where clause?the equivalent of the compact and repair in SQL is to Shrink..but you should only do this if it is to free up unused space that would otherwise not be used in the future growth of the DB. (generally speaking, not a rule) It won't have a performance benefit similar to why it is a necessity in Access.Read this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90809for some relevant discussion on the fragmentation and such...also read Books online for DBCC SHOWCONTIGThere is no black box answer for this. A lot of factors could affect why the inserted records in the new db are faster performing on the join and where clause than in the original table..such as the presence of a clustered index/Primary key...are there non clustered indexes etc...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-02 : 12:01:15
|
You should put each query in QA and check the execution plans..there is likely a clue in those as well..
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-02 : 15:17:08
|
| Defrag the table by rebuild clustered index then try again. |
|
|
|
|
|
|
query is slow on my db but not copy