SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Help me to access my database more quickly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chieubuon
Starting Member

Vietnam
7 Posts

Posted - 02/20/2013 :  03:41:30  Show Profile  Reply with Quote
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!

Edited by - chieubuon on 02/20/2013 04:03:35

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/20/2013 :  08:24:15  Show Profile  Reply with Quote
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

Vietnam
7 Posts

Posted - 02/20/2013 :  19:37:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/20/2013 :  20:55:02  Show Profile  Reply with Quote
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

Vietnam
7 Posts

Posted - 02/21/2013 :  00:50:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/21/2013 :  08:46:20  Show Profile  Reply with Quote
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

Vietnam
7 Posts

Posted - 02/21/2013 :  08:57:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/21/2013 :  09:11:42  Show Profile  Reply with Quote
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

Vietnam
7 Posts

Posted - 02/21/2013 :  09:17:02  Show Profile  Reply with Quote
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

Vietnam
7 Posts

Posted - 02/21/2013 :  23:04:00  Show Profile  Reply with Quote
Oh yeah! The speed of access now is better! Thank James

Help Me Please!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000