SQL Sub Queries?

[paulgrimshaw]

Hi,

I am using the ODBC connection with Sage Accounts to print barcode labels. It is very slow and I want to try and speed up the sql I am using. The database has over 6000 items, but only 3000 are in use at the moment (cannot delete them). I have a variable "QTY_REORDER_LE VEL" which when 0 means its not in use.

I was hoping I could somehow run through the db stripping old out first and then doing the barcode search.

Would this make things faster? And if so/or not, could you give me some ideas.

My current sql string is:

SELECT DESCRIPTION, SALES_PRICE, TAX_CODE
FROM STOCK
WHERE QTY_REORDER_LEVEL <> '0' AND STOCK_CODE = :VarBarcode

Thanks,
Paul.

[azimmer]

Hi,

I am using the ODBC connection with Sage Accounts to print barcode labels. It is very slow and I want to try and speed up the sql I am using. The database has over 6000 items, but only 3000 are in use at the moment (cannot delete them). I have a variable "QTY_REORDER_LE VEL" which when 0 means its not in use.

I was hoping I could somehow run through the db stripping old out first and then doing the barcode search.

Would this make things faster? And if so/or not, could you give me some ideas.

My current sql string is:

SELECT DESCRIPTION, SALES_PRICE, TAX_CODE
FROM STOCK
WHERE QTY_REORDER_LEVEL <> '0' AND STOCK_CODE = :VarBarcode

Thanks,
Paul.

Not necessarily. I suggest you put indices on QTY_REORDER_LEV EL and STOCK_CODE.

[paulgrimshaw]

Hi,

Thanks for your advice, I am going to go and try it.

Paul.