Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Search very slow please help


SQL Search very slow please help

Author
Message
Mike.brickles
Mike.brickles
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Hi

I have couple of SQL Tables with customers information the database works fine on SQL server 2005 but when doing a search when the tables are under SQL 2008 R2 SP2 it runs really slow example I type couple of letters in the search field seems to take its time to input what I have typed like really bad lag

anyone got any ideas please.

Thanks
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10215 Visits: 9549
Mike.brickles (11/1/2013)
Hi

I have couple of SQL Tables with customers information the database works fine on SQL server 2005 but when doing a search when the tables are under SQL 2008 R2 SP2 it runs really slow example I type couple of letters in the search field seems to take its time to input what I have typed like really bad lag

anyone got any ideas please.

Thanks

Hello and welcome to the forums. You aren't giving us much to go on here. We can't see what's on your screen, so we don't know anything about what you're using or encountering other than what you post. Here's a few questions to get started:

1. What search field are you typing into? Is this an application you've written?
2. How's the performance of the query in in SSMS? That will eliminate any extra variables and narrow the execution down to the database.
3. How did the tables and data get from 2005 to 2008? If the indexes aren't there in 2008, that will cause performance problems.
4. Can you quantify "like really bad lag"?

In order to troubleshoot a query, we'll need some readily-consumable DDL, data, expected results and what you've tried so far. Please see the link in my signature for advice on how to post questions to the forum.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Mike.brickles
Mike.brickles
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
I moved the tables by using the import and export data

I really dont know much about sql at all
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
Mike.brickles (11/1/2013)
I moved the tables by using the import and export data

I really dont know much about sql at all


Are the 2 schemas the same? Indexes, statistics? Have you reindexed the tables? Are statistics up to date?

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Mike.brickles
Mike.brickles
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
how do i index the tables ?
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10215 Visits: 9549
Kurt W. Zimmerman (11/1/2013)
Mike.brickles (11/1/2013)
I moved the tables by using the import and export data

I really dont know much about sql at all


Are the 2 schemas the same? Indexes, statistics? Have you reindexed the tables? Are statistics up to date?

Kurt

That's it. When you use the import/export wizard, the table structure and data are copied, but the keys, constraints and indexes are not. You'll need to update the table on 2008 to match the one in 2008 to be able to compare apples-to-apples.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10215 Visits: 9549
Mike.brickles (11/1/2013)
how do i index the tables ?

In 2005, generate the script to create the table by using Script Table As...Create To...New Query Editor window. Then connect to your 2008 database. You already have the table created and populated, so you can't create it unless you drop it first, which would result in removing all the data. So, only run the appropriate commands in the script to create the indexes.

Next time, I would create the table first and get the indexes and constraints created first, then use the import/export wizard to copy your data over to 2008.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
If I'm going to move data from one server to another via Import/Export I will script out the source database first and place it on the destination box. Then when I move the data from source to destination at least I know the 2 schemas are the same. Finally I reindex all of the indexes and update the statistics before querying the destination tables.

Unless the source & destination boxes are exactly the same, it has been my experience that the 2 boxes will not respond the same. There are so many issues that determine performance that I've realized that the 2 will be different. But if there is a difference in minutes or hours then chances are there is something I've might have overlooked.

Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10215 Visits: 9549
Kurt W. Zimmerman (11/1/2013)
Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.

Amen to that. Problems always show up where they should when the test hardware is less then production. Once you have it running well in test, then there won't be any nasty surprises on production. Plus, you get to be pleased with how well your code performs in production. :-D


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
kiril.lazarov.77
kiril.lazarov.77
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 2248
Kurt W. Zimmerman (11/1/2013)
If I'm going to move data from one server to another via Import/Export I will script out the source database first and place it on the destination box. Then when I move the data from source to destination at least I know the 2 schemas are the same. Finally I reindex all of the indexes and update the statistics before querying the destination tables.

Unless the source & destination boxes are exactly the same, it has been my experience that the 2 boxes will not respond the same. There are so many issues that determine performance that I've realized that the 2 will be different. But if there is a difference in minutes or hours then chances are there is something I've might have overlooked.

Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.

Kurt


Just out of interest..why would you update statistics in this case?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search