Descending Sort on index

I have a unique index based on the following columns:
ProjectID (int)
MaterialCatalogID (int)

Material catalogues are pretty much static but projects are dynamic and
people are most likely to be working on the latest project so would
using a descending sort on the ProjectID in the index gain any
performance?You'll probably have to give some more details of what your data looks
like, what your most frequent queries and data modifications are etc.
But changing the order of the index is probably only really useful when
you have a lot of queries which return results in that particular
order. You could always try it out on a test server, of course.

Simon|||An index can be traversed both ascending and descending. This means that
changing the index order (in the index definition) is never useful for
single column indexes.

If you have compound indexes, then the order can influence performance
of some very specific queries. Generally I would not worry about the
index order.

Gert-Jan

Trevor Best wrote:
> I have a unique index based on the following columns:
> ProjectID (int)
> MaterialCatalogID (int)
> Material catalogues are pretty much static but projects are dynamic and
> people are most likely to be working on the latest project so would
> using a descending sort on the ProjectID in the index gain any
> performance?

Derived Column Calculation

Example: (47 / 204709) * 12000 = 2.754

Both values (47 & 204709) are of data type Int.

SQL Sever - Derived Column Calculation returns 2.74

The destination data type is Float

I have converted the data types to Float, Decimal & Numeric and still got the same answer.

Any suggestions

Have you casted the result of the calculation to a float?

-Jamie

|||

Yes, I have what I have noticed with the Data Convertion component is that the input and output data types stay the same. Should it not change to the new data type.

|||

Can you post your expression up here?

-Jamie

|||

Michael Exall wrote:

Yes, I have what I have noticed with the Data Convertion component is that the input and output data types stay the same. Should it not change to the new data type.

No. If you were changing the data-type of a column then inherently you are changing the "shape" of the memory buffer and therefore this would need to be an asynchronous component.

A design choice was obviously made to keep data conversion component synchronous for perf reasons.

If you're puzzled as to the difference between synch and asynch...google it http://www.google.co.uk/search?hl=en&q=ssis+synchronous+asynchronous&meta=

-Jamie

Dependencies not correct with temporary tables --> replication is failing

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@.GuichetId int,
@.Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@.GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @.GuichetID, @.Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @.GuichetID, @.Validateur

SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO

The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable

spU_GUI_AppliquePerteFinancement

spU_GUI_AppliquePerteAgrement

FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

Did the error happen when you are applying a snapshot or when the distribution agent is applying a DDL change for the stored procedure update? If this happened while the snapshot was being delivered to the subscriber, I have the following questions for you:

1) Are you using 'drop' as the pre-creation command for the stored procedure articles? If so, you should theoretically not be seeing the error that you saw when the snapshot was delivered to the subscriber since the referenced procedures would either have been drop (or recreated with the new definition).

2) Are the referenced stored procedures (spU_GUI_AppliquePerteFinancement and spU_GUI_AppliquePerteAgrement) included in the publication as well? (It never hurts to ask the obvious.)

3) It would be great if you can post history messages of the distribution agent when the snapshot was applied, I just want to see the relative order of how the objects that you mentioned are applied. I will also be interested to see the history messages from the snapshot agent.

There is an undocumented -EnableStoredProcedureDependenciesReevaluation 1 switch in the snapshot agent which forces the snapshot agent to compute dependency ordering for stored procedure articles in a more accurate manner but I have a feeling that the real problem is something else.

-Raymond

|||

Hello Raymond,

Indeed there seems to be problems with the dependencies between stored procedures and tables, but the main issue here seems to be to be related to the latest problem we spoke together

After isolating the problem, I've found that the stored procedure that could not be replicated contains a "INSERT INTO" statement.

I will try a work-around by playing with post-replication scripts, while waiting for SP2 ;-)

Anyway, I will investigate the solutions you give for the dependencies

Pierre-Emmanuel