Databases
StackOverflow2013
Postsdbo
POTable searches fast on local SQL Server but slow on host SQL Server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTable searches fast on local SQL Server but slow on host SQL Server
    primarykey
    data
    text
    <p>I have a SQL Server 2008 database with a table that contains about a 100,000 rows, I have setup all the correct indexes and primary keys and Full Text Search and made sure its fine tuned.</p> <p>I created a stored procedure that searches a nvarchar column in the table, the SP is long and has multiple select / insert statements.</p> <p>Now when I run the SP on my local SQL Server I get the result in one second, then I backed up the database and restored it on another machine and tried the same search and I got it in one second.</p> <p>My problem is when I create the database in my hosts shared SQL Server and import all the data from my SQL Server and run the search I get the result in 7 seconds! although I made sure I have the same indexes and primary keys and FTS working.</p> <p>The only difference in my database in the hosts sql server is that I created the schema using a script then I imported all the data using the import/export wizard, so to check if this is whats causing this problem I created a new database on my machine and imported the data using the wizard, and I got the search result in 3 seconds!</p> <p>I checked the execution plan of the SP in the database on the hosts SQL Server and found one index scan is taking 49% of the cost while on my machine it was 0%</p> <p>Can somebody please point out why this is happening?</p> <p>Thanks.</p> <p>EDIT: </p> <p>Now when I run the execution plan for both servers I get exactly the same plan and costs, but still the speed difference is huge (1 second and 7 seconds).</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAmjadoV
    1. USAmjadoV
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHow are you calling the stored procedure on the Shared Host? from SQL Management Studio or from a web page on your shared host?
      singulars
      1. POTable searches fast on local SQL Server but slow on host SQL Server
      1. USRob
    2. COCould you post the query please? If both your local SQL and the Prod server are doing table scans, then your indexing might not be 100%.
      singulars
      1. POTable searches fast on local SQL Server but slow on host SQL Server
      1. USStuartLC
    3. COI'm calling the Stored procedure from SQL Management studio and passing the search query. is there a way to check if the table index has a problem?
      singulars
      1. POTable searches fast on local SQL Server but slow on host SQL Server
      1. USAmjadoV
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload