Query Process is Slow Process

This query cannot be refined much further because it is in the simplest form.

Add table indexes on the columns used in where clause. This will make a huge difference.

Read this to know how to add indexes in Access: http://support.microsoft.com/kb/304272

why you group them? I dont see any aggregation here...

use distinct/unique and get rid of group by

RSL.Open "Select unique BD.BiltyNo, ...., where ... order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic

ie add "unique" after select and delete "group by BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST" from query...

if unique gives error, use "distinct"

HainKurt:
thanks to shorten my query, but its taking same time

A question: are your two tables supposed to be joined in any way? Currently you are extracting from Bilty_Detail and Transp_City without specifying how they are connected. Generally it's a good idea to use an inner join, or a left join, which connects the tables on a common indexed field value.

Otherwise you're going to end up with a query that takes an age to run, and produces results that you're not expecting.

plummet
>>>A question: are your two tables supposed to be joined in any way? <<<<
Relationship created between Bilty_Detail.To and Transp_City.City_Name
and both fields are not indexed
 

so, you removed group by and you say no improvement! :) there should be, but maybe not so much...

can you please post the shortened version again...

also I did not like that or in the where clause...

it is confusing right now, try to use (..) like:

(this and that) or (this and this and this)

or

this and that and (this or that) and this

Select BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark AS PM_No, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST 
   from Bilty_Detail as BD, Transp_City as TC 
  where CST =  '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.City_Name = BD.To and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0 
  order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic

Select allOpen in new window

As your suggestion i removed Group by and  added Distinct
 

RSL.Open "Select distinct(BD.BiltyNo), BD.To, BD.Article, BD.Pvt_Mark AS PM_No, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST from Bilty_Detail as BD, Transp_City as TC where CST =  '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.City_Name = BD.To and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0 order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic

Select allOpen in new window

<<<< (this and that) or (this and this and this) >>>>

little confuse can you please explain me with my query.


and Thanks for quick reply

remove parantheses:

RSL.Open "Select distinct(BD.BiltyNo), BD.To, BD.Article, ...
-->
RSL.Open "Select distinct BD.BiltyNo, BD.To, BD.Article, ...

this is your where part

CST =  '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.City_Name = BD.To and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0

and there is an or in the middle... so maybe rewriting this part helps... I cannot predict what to group conditions here

basically you have this:

a and b or c and d and e and f

first use join conditions "TC.City_Name = BD.To" then filtering conditions... are you happy with current result? are they correct? how many result do you get?

ok  removed parantheses

yes my current result is very right

ok
as you suggested
<<<first use join conditions >>>
i did but result is wrong

where TC.City_Name = BD.To and CST = '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0

If the 2 tables are linked why isn't there a join in the query?

Without that you'll end up with a cartesian product as the result.

That means if there are X records in one table and Y in the other you will end up with X*Y records returned.

Adding criteria, grouping etc might eventually give you the result you want.

<<imnorie:>>
i am getting result i want, but  its takes time to display  result.

Yes, basically because of what I posted.

imnorie
please read my original question.


<<imnorie:>>
i am getting result i want, but  its takes time to display  result.
it means i am already getting my result.
problem is not with the result
problem is it takes time to display result.

Yes, and the reason it takes so long to get the results is because there is no join in the query.

Adding the criteria, grouping etc is giving the correct results but these things take time.

A join would look something like this:

FROM TC LEFT JOIN BD OB TC.City_Name = BD.To

Please Provide exact query as i posted above with your suggestion (Join..)

hey hey..........wow
i thing i got result
HainKurt  O Man...

I don't like mixing many "and" & "or" s without using "(...)" It is just confusing...

let me test again with different data
speed like formula 1 CAR.. :-D

if the result is wrong, try to explain where condition in plain english so we can understand where to put (...) and where to put that or (which creates the issue)

Man i am not gonna give even 150 points

you know what
you changed whole my  shit logic (sorry )

damn i tried so hard to get that group query, and you destroy it within a minutes.
my whole application was depending on that query,
just the problem was it takes long to display result

"shit logic" --> no logic :)

yes you can say that "NO Logic"

you deserve 1000 with grade AAA

Thanks
i Tested this query with Different  MDB file (Bigger than first one)
and its working like
"ALREADY DATA WAS THERE"
it taking even less than a second

9470 records

i want specific 2000 record

with my group by query  was taking 2 to 4 miniutes

but with you logic it takes less than seconds

Thanks  A  LOT