locked
Optimizing database query execution process RRS feed

  • Question

  • I have two server in my company located in different locations say Location A and Location B. Both the servers log the web proxy logs for each of the user browsing the internet. The details of the users are logged to either server which is nearer depending upon the location of the user.

    I need to create some reports based on  consolidated data because users data is on two different server.

    I need to have some reports, showing the details of data being received or sent for a user in internet browsing.

    Now the biggest challenge which I have now:

    One of the server has millions of record and another has records in thousands. I have to consolidate the records at one server say A. Now the Server A will be using server B as Linked Server.

    When I query something on server A it takes 3-4 minutes even more just to get the data for me. I have created proper indexes as well. What is the best way to design this?. How can I optimize the query operation cost? What could be done in order to improve efficiency.

     

    Thanks and Regards,

    Ashraf

    • Edited by Ashraf Ansari Thursday, July 8, 2010 1:26 PM Spelling mistakes
    Thursday, July 8, 2010 1:22 PM

Answers

  • The best way is to create a data warehouse based on merged data from both servers and run a report based on pre-aggregated data in a data warehouse.
    But it requires running SSIS and SSAS.
    Use right technology to get the best results.


    Sergei
    Friday, July 9, 2010 10:01 AM
  • I agree with Sergei, to create a new Data warehouse and merge the data. If you really concern about the performance then you need to consider the new optimized DW option.

    The benefit is not to disturb the existing DB’s. Also when you create the new DW you can design better way. I mean you can define your own strategy of DB/Table partitioning, Index planning, File groups and other options.

    So it is always suggestible to for a single DB query rather than Multiple DB interaction query. And even you try a lot to optimize the cross db query but it will not give you the best performance as you can expect in a single DB.

    Thanks,

    Sandeep

    Tuesday, July 13, 2010 7:19 AM

All replies

  • The best way is to create a data warehouse based on merged data from both servers and run a report based on pre-aggregated data in a data warehouse.
    But it requires running SSIS and SSAS.
    Use right technology to get the best results.


    Sergei
    Friday, July 9, 2010 10:01 AM
  • Can  you consolidate a biggest table on its server and SELECT from the linked server the smallest table?

    BTW what version are  you using?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 11, 2010 7:04 AM
  • I agree with Sergei, to create a new Data warehouse and merge the data. If you really concern about the performance then you need to consider the new optimized DW option.

    The benefit is not to disturb the existing DB’s. Also when you create the new DW you can design better way. I mean you can define your own strategy of DB/Table partitioning, Index planning, File groups and other options.

    So it is always suggestible to for a single DB query rather than Multiple DB interaction query. And even you try a lot to optimize the cross db query but it will not give you the best performance as you can expect in a single DB.

    Thanks,

    Sandeep

    Tuesday, July 13, 2010 7:19 AM
  • Yes i am able to do that.
    Tuesday, July 13, 2010 12:54 PM
  • Hi Sergei,

    Your option is good but does not suits my current requirements.

     

    Tuesday, July 13, 2010 12:56 PM
  • Let me ask-- are you looking just for improvements in the SQL right now?

    Wednesday, July 14, 2010 8:31 PM