DB HUGE! Help on indexes and ideas

[Kristen]

@sapator

if i do not use [DISTINCT], i get millions on rows, in comparison of thousands that I get with using the distinct.

So code it so that there are no duplicates - e.g. change the JOINs that currently match multiple rows so that only match a single, representative, row.

@sapator

can't understand what you mean by DUP's. I don't know the technical term. Is it duplicate rows?

Apologies, yes "DUPs" was my abbreviation for "duplicate rows".

@sapator

This is ( ) the 3rd party developer tables and we cannot mess with them.

This is why I think you should consider pulling all the 3rd party database into your "Enquiry database".

maybe just try it with a couple of tables (the ones used in this query) and see how your revised query peeforms:

INSERT INTO MyEnquiryDB.dbo.tblStock
SELECT *,
    [VStock_strBookletIdent_LEFT] = LEFT(VStock_strBookletIdent,4),
    [VStock_strBookletIdent_RIGHT = CONVERT(numeric(99,9), RIGHT(VStock_strBookletIdent,7)),
    ... etc ...
FROM VISTAVM.DBO.tblStock

and then create an index (this is just an suggested example, not a real index create statement )

CREATE INDEX MyIndexName ON MyEnquiryDB.dbo.tblStock
(
    VStock_strBookletIdent_LEFT
    , ...
)

Assuming that goes well then your task will change to become the best possible way of "copying" data from the 3rd party database to your Enquiry database (e.g. if you need your enquiry database to be "no more than 1 minute old". Obviously if your enquiry database can be "24 hours old" that problem would be much easier to solve!)