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.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Help me to access my database more quickly

Author  Topic 

chieubuon
Starting Member

7 Posts

Posted - 2013-02-20 : 03:41:30
Hi all

I have a problem with my database access speed.

Last month, I have only one Server (Server A). I installed IIS7 and SQL Server 2005 into this Server. I have a database with 1 table of about 3,000,000 records. I created an ODBC to connect with this table and view data on website by ASP. The speed of data access is OK.

Now, I have new more Server (Server B, configuration is better than A). I installed SQL Server 2008 on B, move all data from A to B. From A, I created ODBC to connect data to B and view data by asp (IIS on A). But data access speed is very slow! I dont know why?

Please help me how to do now?

Thank you

Help Me Please!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 08:24:15
If the databases are identical (i.e., the table structure is the same, they have the same indexes etc.), and assuming the server is "better" than the old server (memory, disk performance, cpu's etc.): Check if you are running maintenance plans on the new server. Index fragmentation and out of date statistics are two things that you can check very easily (google).
Go to Top of Page

chieubuon
Starting Member

7 Posts

Posted - 2013-02-20 : 19:37:03
Thank James,

My database is not indexed. I do not know it will be more quickly or not to execute command SELECT FROM WHERE if i create some indexes ? Do you thing some other reason?

Last month, SQL Server (but version 2005) and IIS on the same server (old server), database is not indexed, but the speed of process is acceptable

Help Me Please!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 20:55:02
Creating appropriate indexes will help speed up the queries. If the original database had no indexes, and if the new one does not, there are factors other than indexes that are affecting performance. Check if you have updated statistics in your database. Does the new server have the same hardware specifications (memory, cpu cores etc.?)
Go to Top of Page

chieubuon
Starting Member

7 Posts

Posted - 2013-02-21 : 00:50:16
Yes, new server has hardware specification better (old server RAM is 8G, new server is 16G, CPU and HDD also better...).

Example if I have a table mytable (id, name, birthdate, address, tel, fax) and i have 3 million records.

Now I have no index for this table, and I run command SELECT * FROM MYTABLE WHERE (ID>0) AND (NAME LIKE 'JOHN%') AND (BIRTHDATE >= '20090821') AND (BIRTHDATE < '20130822').

If I want above command run more quickly, how can I create index file? Do you mean I have to create 3 indexes concerning ID, NAME and BIRTHDATE ?

Help Me Please!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 08:46:20
If that is the query that you are most concerned about, and if similar indexes do not already exist, you can create an index on name, birthdate and id. However, creating too many indexes can be detrimental too, so do that only if that is the query that you most want to speed up.

http://msdn.microsoft.com/en-us/library/ms188783.aspx this page has examples and documentation on how to create an index. You can also create index using SSMS. In object explorer, expand the table name node and right click on Indexes node.
Go to Top of Page

chieubuon
Starting Member

7 Posts

Posted - 2013-02-21 : 08:57:57
I know how to create index file, but I dont know how to use it. May I have to change something in above command for using index file?

Help Me Please!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 09:11:42
Once you create the index, you should not have to do anything specifically for the query to use it. You can use table hints to specify that a certain index be used, but usually there is no reason to, and usually it is better to leave the query optimizer to its own devices. Run the query before and after the indexes and you should see the difference in performance.
Go to Top of Page

chieubuon
Starting Member

7 Posts

Posted - 2013-02-21 : 09:17:02
Ok. I will do as you say then let you know the result later

Help Me Please!
Go to Top of Page

chieubuon
Starting Member

7 Posts

Posted - 2013-02-21 : 23:04:00
Oh yeah! The speed of access now is better! Thank James

Help Me Please!
Go to Top of Page
   

- Advertisement -