Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Search very slow please help Expand / Collapse
Author
Message
Posted Friday, November 1, 2013 5:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 9:47 AM
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
Post #1510554
Posted Friday, November 1, 2013 6:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 3,981, Visits: 2,998
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
Post #1510567
Posted Friday, November 1, 2013 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 9:47 AM
Points: 5, Visits: 7
I moved the tables by using the import and export data

I really dont know much about sql at all
Post #1510591
Posted Friday, November 1, 2013 8:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 984, Visits: 1,324
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
Post #1510606
Posted Friday, November 1, 2013 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 9:47 AM
Points: 5, Visits: 7
how do i index the tables ?
Post #1510607
Posted Friday, November 1, 2013 8:27 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 3,981, Visits: 2,998
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
Post #1510609
Posted Friday, November 1, 2013 8:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 3,981, Visits: 2,998
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
Post #1510612
Posted Friday, November 1, 2013 8:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 984, Visits: 1,324
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
Post #1510622
Posted Friday, November 1, 2013 9:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 3,981, Visits: 2,998
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1510631
Posted Friday, November 1, 2013 9:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:55 PM
Points: 275, Visits: 1,666
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?
Post #1510633
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse