query against vldb

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Peja (anonymous_at_discussions.microsoft.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 09:56:34 -0700

Hi,
we have 1.1 TB database, stored on sql server 2000, 4
procs, 6GB of memory (5GB assigned to sql server). I have
one query to tune. I got it to response time of 1.5
hours, but it is not acceptable. Statistics are updated
when creating indexes, and after that database is being
put into read_only mode. Does anybody have an idea what
else can be done?

Below is the query. Two bigest tables are A and B (10GB
and 8GB). Both have clustered indexes created on ID
columns. "A" table also has separate nonclustered index
for each CODE_ column. "G" table has 10,000 rows (query
optimizer starts from join between A and G - and it goes
into A clustered index scan).

SELECT A.CODE,
        A.ADDRESS,
        A.CITY,
        A.PROVINCE,
        A.ZIP,
        A.MAIL_ZIP AS "MAIL ZIP",
        C.DESCRIPTION,
        A.NUMBER,
        D.DESCRIPTION,
        E.DESCRIPTION,
        A.TOTAL,
        F.DESCRIPTION,
        A.DESCRIPTION,
        A.DIG_CODE,
        G.DESCRIPTION,
        A.REGDATE

FROM DEVICE A LEFT JOIN POPS C
ON A.CODE_POPS=C.CODE
  
LEFT JOIN TARS D
ON A.CODE_TAR=D.CODE
  
LEFT OUTER JOIN CCD F
ON A.CODE_CCD=F.CODE

JOIN STAT G
ON A.CODE_STAT=G.CODE

LEFT OUTER JOIN COMP B
ON A.ID=B.ID
WHERE
B.ID1 = '165985890' OR
 B.ID2 = '165985895' OR
 B.ID3 = '165985899' OR
 B.ID4 = '165985990'
 

Thanks,
Peja

Relevant Pages

  • RE: full-text search failed with one million of key words (Sqlserv
    ... Research with "toto" was just a test. ... In real life, my database contains ... for only one book) And the table i want to query looks like this: ... I have to make big tests with sql server 2005 express (i'm waiting credits ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Error 25081: Incorrectly generating identities after a merge
    ... database similarly to SQL Server CE Replication. ... On the SQL Server 2000 database with replicated tables from the original ... The I synchronized that database and ran the same query: ... >> synchronization. ...
    (microsoft.public.sqlserver.ce)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)
  • Re: Using a worksheet as a table in an SQL query
    ... I'll probably just dump the SQL Server data to a spreadsheet and join the two spreadsheets. ... You could use Access to link to both sources and create a crosstab query. ... There are ways to enter the data into their database via the application, but it would require them to go to many different windows instead of being able to enter the data all on one screen. ... There is not a great deal of data they would enter on the spreadsheet, but I'm not sure how slow that would make the query. ...
    (microsoft.public.excel.programming)
  • Re: CONTAINS performance
    ... How can I predict the scalability of the simple query given that I don't ... but will measure the FTS with multiple clients issuing random FTS queries. ... B on A.bid=B.id left outer join ... the SQL Server query optimizer executes this query in the optimal manner ...
    (microsoft.public.sqlserver.fulltext)