| Author |
Topic |
|
chieubuon
Starting Member
7 Posts |
 Posted - 2013-02-20 : 03:41:30
|
| Hi allI 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 youHelp 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). |
 |
|
|
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 acceptableHelp Me Please! |
|
|
|
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.?) |
|
|
|
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! |
|
|
|
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. |
|
|
|
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! |
|
|
|
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. |
|
|
|
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 laterHelp Me Please! |
|
|
|
chieubuon
Starting Member
7 Posts |
Posted - 2013-02-21 : 23:04:00
|
| Oh yeah! The speed of access now is better! Thank JamesHelp Me Please! |
|
|
|
|
Help me to access my database more quickly