Posted by Hugo Kornelis on 09/28/05 00:30

On 27 Sep 2005 09:35:55 -0700, Matik wrote:

>Hi,
>
>I have a small theoretical issue.
>I have one table, which is prettyu large. There is lot of evaluations
>running on this table, that's why, each process need to wait for
>another to be finished. Sometimes, for some critical functions, it
>takes to long time.

Hi Mateusz,

If the processes are only reading the data without modifying it, then
there is no need to wait. They can run concurrently.

>My question is, will it improve performance, if I will create second
>table, exactly like this one, and I will split some evaluations, that
>the one, which defenately need to run on the source table will run on
>the first one, and the second evaluations, will run on the other one.

I doubt it. SQL Server doesn't know that the data in both tables is
equal. So if one query reads row #12345 from table #1, and the other
query reads row #12345 from table #2, SQL Server will fetch the
corresponding data from both tables from disk to cache. In short, you
are effectively halving the amount of cache SQL Server can use for these
queries. I expect performance to decrease.

>To keep data consistance between this two tables, I was thinking baout
>trigger on insert on the mother table, which will transport the data to
>another one.

And this will hurt performance even more. The speed of inserts will slow
down because the trigger has to be executed. As a result, locks on the
main table will live longer, keeping other queries blocked for longer
amounts of time. And the second table will be blocked as well.

Since the data is apparently updated while you are querying it, you
might find benefit in a variation on your idea: make a copy of the
table, but don't use triggers to copy over all modifications. Instead,
set up a job that will periodically synchronise the data. Now make sure
that all queries that don't need up-to-the-second precision are used on
the copy table (that is only update periodically).

>Second part is: to improve selects on the table, should I set indexes
>with option of Fill factor as possible close to 100% or as possible
>close to 0%. Or maybe should I set the pad index option?
>
>What about clustered indexes. Is it better to use them if I would like
>to increase performace for selects?

There is no magic bullet here. Each problem needs it's own solution,
that's why there are so many options.

Read more about performance at www.sqlserver-performance.com, or post
here with full details of your tables, indexes, queries and execution
plans for more advise.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]