5 Replies - 3826 Views - Last Post: 02 December 2012 - 09:11 PM

#1 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 391
  • Joined: 04-January 11

Improve performance on remote query

Posted 29 November 2012 - 01:33 AM

I am not so much of a beginner to MSSQL but I only know the basics of querying and joining tables.

The day before yesterday, I started learning to create clustered and non-clustered indeces with the help of google searches. Today, I am trying to learn to read the execution plan and I notice that it takes a lot of time in the remote query.

For the 300,000+ records-table which I call via a view, the cost of running the remote query is 99% with Actual Number of Rows equal to 286,736. I used this table in a left join. Estimated operator cost is 103.64(99%).

To be honest, I am still confused with these figures since I am still starting to learn to read the graphical representation so I hope someone could guide me. This 103.64 estimated operator cost, is it too much?

The remote table is usually either directly called in the stored procedure or called in a view.

I understand that this really should take a lot of time since there is an expensive cost to query remotely and since the tables I am calling remotely really do have a lot of rows.

I call one table at a time though. One table contains more than 5 million rows while the other has more than 300 thousand. I use these tables for filtering or joining.

I don't know if the tables I am calling remotely are clustered or not and I have no way of knowing due to the lack of access.

Now, I tried adding cluster to the view but I get the error that it is not schema bound.

I am not sure what to do. My goal is to improve performance of my website. It usually takes 30-35 seconds to execute the query. When I added clustered index to one of my local tables used in joins, performace speeds up and time it takes to run query decreased to 21-24 seconds.

Now, this is still too slow for a web application as I believe.

Maybe you, guys, could give me some tips or step-by-step guidelines or checklist to follow in trying to improve performance. I am not really of a database person. I am more to front-end development than the back-end so my database skills are really basic.

Is This A Good Question/Topic? 0
  • +

Replies To: Improve performance on remote query

#2 rnty  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 145
  • Joined: 14-August 12

Re: Improve performance on remote query

Posted 29 November 2012 - 03:46 AM

Normalization of the database.
Was This Post Helpful? 0
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Improve performance on remote query

Posted 01 December 2012 - 01:22 PM

Sounds like you're doing something to fetch the records one at a time, and taking the performance hit because you have a round trip for each record. Another approach is to fetch the remote data without any joins involved, store it to a local temp table, and then run your full query against that.
Was This Post Helpful? 1
  • +
  • -

#4 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 391
  • Joined: 04-January 11

Re: Improve performance on remote query

Posted 02 December 2012 - 08:26 PM

Thanks for the answer guys.

The database was already normalized so I do not think that's the problem.

I did what BobRodes said. I declared a table, filtered the remote table, inserted the results to the temp table, and then ran the query against it.

From 24 seconds, it's only taking 8 seconds to run the query now.

Thank you.
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Improve performance on remote query

Posted 02 December 2012 - 08:48 PM

Are you able to post the query? If so, I can probably help with indexes that will work. There are several types of indexes you could apply, and correct use of indexed columns and included columns can make a world of difference. You need to index the columns that are used as predicates and/or clauses, and then include columns that are selected. This is a feature in MSSQL that I don't think is in many other DBs (it might be in Oracle, not sure).

As an example, for this query:
SELECT
  Items.ID AS ItemID,
  Items.Description AS Item,
  ItemTypes.ID AS ItemTypeID,
  ItemTypes.Description AS ItemType
FROM Items
INNER JOIN ItemTypes ON ItemTypes.ID = Items.ItemTypeID
WHERE Items.PurchaseDate BETWEEN '2012-01-01' AND '2012-02-01'


...I would use the following indexes...
Items(
  ItemTypeID [predicate],
  PurchaseDate [clause]
), include(ID, Description)

ItemTypes(
  ID [predicate]
)include(Description)


Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Improve performance on remote query

Posted 02 December 2012 - 09:11 PM

View PostNovember-06, on 02 December 2012 - 09:26 PM, said:

Thanks for the answer guys.

The database was already normalized so I do not think that's the problem.

I did what BobRodes said. I declared a table, filtered the remote table, inserted the results to the temp table, and then ran the query against it.

From 24 seconds, it's only taking 8 seconds to run the query now.

Thank you.

Wow. I feel smart. :smartass:

This post has been edited by BobRodes: 02 December 2012 - 09:14 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1