SQL Query Query

Born To Be Mild

Looking for a bit of help extracting from a database. Pervasive db and massive tables. Trying to extract all details of orders between two dates. The query keeps failing and corrupting the table which takes hours to rebuild. I'm wondering if it's better to use a join?

I've been running this query:

select * from InvoiceLines
where InvoiceID in (select InvoiceID from InvoiceHead
where InvoiceDate > '2008-12-31'
and InvoiceDate < '2011-01-01')

which runs for a few hours then dies.

I'm wondering if it would be more efficient to use a join query? I rarely use joins so I'm not sure of which type to use etc. Any help would be much appreciated.

yoyo

Moved to Development

Aswerty

Yes joins would be more efficient than using subquerys like you are now. Joins are easy enough to understand.

I modified a query I had to suit what you're doing. Something like it should work.
Also have a google of full, inner and outer joins to make sure you're getting the exact records you're after.

SELECT * FROM InvoiceLines
FULL JOIN InvoiceHead
ON InvoiceLines.InvoiceID = InvoiceHead.InvoiceID
WHERE InvoiceDate BETWEEN '2008-12-31 00:00:00' AND '2011-01-01 23:59:59.997'

Giblet

use a join, not in, you probably have horrible tablescan problems.

amen

Joins are good.

InvoiceDate is a date then remove then declare a start/end date as datetime and assign them your start/end date and use the variables in your query.

do you really need to do select * ? Bet you don't.

Try selecting only the columns you need.

The query keeps failing and corrupting the table which takes hours to rebuild. I'm wondering if it's better to use a join?

This is worrying. A select statement should have not corrupt the table (now it may impact the performance of inserts/updates but thats a different issue).

Are you sure that maybe you query tool is timing out due to the volume of data your returned?

How many invoices are you trying to get

Try

[PHP]
SELECT
COUNT(InvoiceLines.InvoiceID )
FROM
FULL JOIN InvoiceHead
ON InvoiceLines.InvoiceID = InvoiceHead.InvoiceID
WHERE InvoiceDate BETWEEN '2008-12-31 00:00:00' AND '2011-01-01 23:59:59.997'
[/PHP]

This will let you know how many invoices you are getting.

If this is work I would ask them do they really really want all this data?

If so and depending on the volume I break it down by month (maybe even week).

Silly question but are you running this query while the database is in use taking new invoices and if so how busy is your database (Transactions/PerSecond?)

croo

amen wrote: »

[PHP]
SELECT
COUNT(InvoiceLines.InvoiceID )
FROM InvoiceLines
FULL JOIN InvoiceHead
ON InvoiceLines.InvoiceID = InvoiceHead.InvoiceID
WHERE InvoiceDate BETWEEN '2008-12-31 00:00:00' AND '2011-01-01 23:59:59.997'
[/PHP]

Did you not forget the InvoiceLines in bold?

re: FULL JOIN
I don't think I ever used that form but perhaps it could be DB specific ... not sure the OP mentions the DB but just in case I normally would use a
LEFT OUTER JOIN
To join the invoices lines to the Invoice header.
If I undertand the FULL JOIN it will return the Invoice Line even if it has no header - but I guess that would be a breach typical DB consistency, i.e. that an invoice line has an invoice header.

Giblet

You may also need to check the Isolation level if this query is in a transaction scope especially if you are seeing corruption.

Aswerty

croo wrote: »

Did you not forget the InvoiceLines in bold?

re: FULL JOIN
I don't think I ever used that form but perhaps it could be DB specific ... not sure the OP mentions the DB but just in case I normally would use a
LEFT OUTER JOIN
To join the invoices lines to the Invoice header.
If I undertand the FULL JOIN it will return the Invoice Line even if it has no header - but I guess that would be a breach typical DB consistency, i.e. that an invoice line has an invoice header.

Think Amen just modded the code I put up and probably just missed the odd word copying.

Re: full join

The query I modified was for SQL Server and its from 18 months ago and I don't do much work in SQL so I haven't a notion what it's for. It's why I mention to look up left and right joins and probably should have added inner and outer to that as well.

A quick google on full join gives: FULL JOIN - Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data. So might only be useful for niche queries.

amen

croo you are correct. I just cut and pasted the code from the prior reply but I missed some.