none
INDEX Issue in Sql Server 2005 RRS feed

  • Question

  • hi friends ,

    I am working on vb.net and  sql server 2005. I have a table which is used for calculation so i create indexes on that table fields.....

    my problem is ....

    Timing is for calculation after indexing.....

    1. i had write a routine in vb.net which creates indexes on the specified tables colmns in database (used query: CREATE INDEX indexName ON tableName(ColmnName))----it takes 30 mins for calculation

    2. if i create indexes using sql server interface(right clicking on field and indexes )-----it takes 10 mints

    Can any one tell me what is the difference bet these two approches or why there is time diff..

    thank you...

    Saturday, October 30, 2010 4:59 AM

Answers

  • There shouldn't be any difference in speed when the command is sent from a VB client or when it is sent from Management Studio, as long as they operate under the same conditions. Of course, the conditions could be different, for instance, it is possible that Resource Governor might be enabled on the server and that it allocates different amount of resources depending on the origin of the query.

    One suggestion that I can provide is that you use the SQL Profiler to capture the queries sent to the server, and then compare the queries that are received from VB with the queries that are received from SSMS, to verify whether they are actually the same. Profiler can also provide some useful stats such as CPU cycles consumed and IO operations, which can also give you some clues as to what may be different between your to ways of launching the index creation.

    Anyway, the Training and Certification forum is probably not the best place to ask this kind of question. You will get better answers if you ask in one of the SQL Server forums on Technet, for example:

    http://social.technet.microsoft.com/Forums/en-us/sqldatabaseengine/threads

    Saturday, October 30, 2010 5:39 AM

All replies

  • There shouldn't be any difference in speed when the command is sent from a VB client or when it is sent from Management Studio, as long as they operate under the same conditions. Of course, the conditions could be different, for instance, it is possible that Resource Governor might be enabled on the server and that it allocates different amount of resources depending on the origin of the query.

    One suggestion that I can provide is that you use the SQL Profiler to capture the queries sent to the server, and then compare the queries that are received from VB with the queries that are received from SSMS, to verify whether they are actually the same. Profiler can also provide some useful stats such as CPU cycles consumed and IO operations, which can also give you some clues as to what may be different between your to ways of launching the index creation.

    Anyway, the Training and Certification forum is probably not the best place to ask this kind of question. You will get better answers if you ask in one of the SQL Server forums on Technet, for example:

    http://social.technet.microsoft.com/Forums/en-us/sqldatabaseengine/threads

    Saturday, October 30, 2010 5:39 AM
  • thank you very much sir....

    i will try it right now n let u know what happens

    Saturday, October 30, 2010 5:57 AM