how to run query which has millions of records in POdetails.

[adnancanada]

I am using access 2013 frontend and backend SQL server. I have below this query. I am running this query via pass through query in Access but because of millions of records it runs continuly or freez but dost not give me output.

Is there any best way to run this query fast. I have vitrual tables. There is no index on it.

What is the best procedre to run this query fast. Kindly help.


SELECT PODETAIL.PODATE, PODETAIL.PONBR, PODETAIL.ITEM, PODETAIL.VDRNBR, PODETAIL.WERKS, PODETAIL.MENGE, PODETAIL.MEINS, PODETAIL.BPRME, PODETAIL.GEWEI, PODETAIL.VOLUM, PODETAIL.VOL, PODETAIL.LTSNR, POHEADER.VDRNBR FROM PODETAIL


INNER JOIN POHEADER
ON
(PODETAIL.PONBR = POHEADER.PONBR)
WHERE POHEADER.VDRNBR = '000250000' AND POHEADER.PODATE BETWEEN 20140101 AND 20151231

[pbaldy]

I know you've cross posted this, but you realize you're pulling 2 years data? Presumably that's a lot of records to cross the network. What are you using this for?

[InsuranceGuy]

Is there any best way to run this query fast. I have vitrual tables. There is no index on it.

Properly index it and SQL/Access will do just fine.

Jeff

[adnancanada]

Properly index it and SQL/Access will do just fine.

Jeff

I tried to create index but I got error message , kindly see error message. Please also note that POHeader and PODetail are virtual tables.
error message:

"Incorrect syntax error near the keyword "WITH", (#156) if this statement is common table experssion or xmlnamespace clause, the previous statement must be terminated with semicolon (#319)"

select query is running fine before where clause.

CREATE VIEW VWPODETAIL
WITH SCHEMABINDING
AS
SELECT PODETAIL.PODATE, PODETAIL.PONBR, PODETAIL.ITEM, PODETAIL.VDRNBR, PODETAIL.WERKS, PODETAIL.MENGE, PODETAIL.MEINS, PODETAIL.BPRME, PODETAIL.GEWEI, PODETAIL.VOLUM, PODETAIL.VOL, PODETAIL.LTSNR, POHEADER.VDRNBR FROM DBO.PODETAIL
INNER JOIN DBO.POHEADER
ON
(DBO,PODETAIL.PONBR = DBO.POHEADER.PONBR)
WHERE DBO.POHEADER.VDRNBR = '000250000' AND DBO.POHEADER.PODATE BETWEEN 20140101 AND 20151231

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I tried this simple one but got error msg.

CREATE VIEW vwPOheader
WITH SCHEMABINDING
AS
SELECT PO, PODATE, VDRNBR, TERM1, TERM2 FROM dbo.ETTO


ERROR MESG:
cannot schema bind with view 'vwPOheader', dbo.ETTO is not schema bound (#4513)

[InsuranceGuy]

You will want to engage a forum focused on SQL Server or a proper DBA since the problem is not MS Access related.