sql query on a query
Hi, I'm using tAdoDataSet to query data from my sql server. It's working fine but now I wan't to do something more complex and I need to query the results like access would do.
For exemple, I'm compiling all invoices in a query. Next I wan't to sum for each customer the total amount invoiced (from my query result).
I could probably do this in one query but I need to make some complex filtering on things like groups, category and prices and I have found that it's really much slower using a where condition vs a having condition (which I must use if I'm grouping to create a sum).
For exemple, I'm compiling all invoices in a query. Next I wan't to sum for each customer the total amount invoiced (from my query result).
I could probably do this in one query but I need to make some complex filtering on things like groups, category and prices and I have found that it's really much slower using a where condition vs a having condition (which I must use if I'm grouping to create a sum).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is your question please?
I will guess it's about:
> where condition vs a having condition
"WHERE" works on each row to filter and to build the result set.
"HAVING" works to filter the result set after it was built, and it works mostly with aggregation (Max, Min, Avg, ...etc).
Every Query has it's own way to use WHERE & HAVING.
So keep in mind HAVING is not synonymous for WHERE to match which is faster. They both work together towards achieving your goal.
For instance, consider this example that shows the difference:
"TableX" is a Table that has one column "Col1" of the type Integer, and has the values (1, 2, 3, 4, 9, 10).
If you want to get the Min value, but only for values greater than three, then your SQL looks like:
Select min(Col1)
from TableX
where Col1 > 3;
Which should get you 4.
What happens is, that all rows with values less than or equal to three are excluded from the evaluation then the Min value is returned out of the selection of rows as result set.
On the other hand, to do the same with HAVING you need to do this:
select Min(Col1) from
(Select Col1
from Tablex
having Col1 > 3) as T;
Which will get you 4 as well.
But all values greater than three are returned first, and then the Min value is determined out of the result set.
Hope this clears the difference.
I will guess it's about:
> where condition vs a having condition
"WHERE" works on each row to filter and to build the result set.
"HAVING" works to filter the result set after it was built, and it works mostly with aggregation (Max, Min, Avg, ...etc).
Every Query has it's own way to use WHERE & HAVING.
So keep in mind HAVING is not synonymous for WHERE to match which is faster. They both work together towards achieving your goal.
For instance, consider this example that shows the difference:
"TableX" is a Table that has one column "Col1" of the type Integer, and has the values (1, 2, 3, 4, 9, 10).
If you want to get the Min value, but only for values greater than three, then your SQL looks like:
Select min(Col1)
from TableX
where Col1 > 3;
Which should get you 4.
What happens is, that all rows with values less than or equal to three are excluded from the evaluation then the Min value is returned out of the selection of rows as result set.
On the other hand, to do the same with HAVING you need to do this:
select Min(Col1) from
(Select Col1
from Tablex
having Col1 > 3) as T;
Which will get you 4 as well.
But all values greater than three are returned first, and then the Min value is determined out of the result set.
Hope this clears the difference.
ASKER
I'm not familiar with stored procedures, how does it work?
jimyX: Basically, my question was how can I work from an adodataset result to query again on it so I can group some data. The speed problem I'm experiencing may not be due to the "having" part, maybe it's simply because I'm grouping data and it's more data to process, this has made me a aware of the speed problem I'm getting on some table and query on a query may help me get around this instead of one big complex query.
thank you for all your help, it's appreciated.
jimyX: Basically, my question was how can I work from an adodataset result to query again on it so I can group some data. The speed problem I'm experiencing may not be due to the "having" part, maybe it's simply because I'm grouping data and it's more data to process, this has made me a aware of the speed problem I'm getting on some table and query on a query may help me get around this instead of one big complex query.
thank you for all your help, it's appreciated.
Well, please post a concise and complete example. Otherwise we can only guess what your doing..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So, since no solution was determined so far, I'm jumping in.
Let's clarify some things:
1) A single query to solve that would be much more faster than using adoDataSet any time. Because the query will run straight on database, with no network to cause slowness;
2) If you state your problem clear, we can decide if your filtering needs are really complex or not;
3) You are not required to use having because you have used group by in your query;
4) Performance could be harmed because you lack indexes on your table for the filter you are using.
Please define your problem again, with examples and any attachments, and I'm pretty sure that you are going to have a satisfactory answer.
Let's clarify some things:
1) A single query to solve that would be much more faster than using adoDataSet any time. Because the query will run straight on database, with no network to cause slowness;
2) If you state your problem clear, we can decide if your filtering needs are really complex or not;
3) You are not required to use having because you have used group by in your query;
4) Performance could be harmed because you lack indexes on your table for the filter you are using.
Please define your problem again, with examples and any attachments, and I'm pretty sure that you are going to have a satisfactory answer.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For performance; this you should NOT do:
WHERE (CONVERT(varchar(10), date, 121) = '2014-03-24')
Do NOT run functions on every row of data to suit a single value.
Instead, change the value(s) to suit the data!
WHERE [date] >= '20140324' and [date] < '20140325'
or
WHERE [date] >= @datevar and [date] < dateadd(day,1,@datevar)
---------------
& you do not need a CTE for this, a simple nested query would do it
WHERE (CONVERT(varchar(10), date, 121) = '2014-03-24')
Do NOT run functions on every row of data to suit a single value.
Instead, change the value(s) to suit the data!
WHERE [date] >= '20140324' and [date] < '20140325'
or
WHERE [date] >= @datevar and [date] < dateadd(day,1,@datevar)
---------------
& you do not need a CTE for this, a simple nested query would do it
ASKER
Thank you Paul, date format has always been a problem since I have some customers that have a date format that is not yyyymmdd, your solution seem to work around this problem quite nicely, I'll be sure to use it from now on :)
did you try group by rollup ?
transact-sql :
https://technet.microsoft.com/en-US/library/ms189305%28v=sql.90%29.aspx
select customer, sum(amount)
from invoices
group by customer with rollup
this will give null for the total
if you want the word "total":
select case when grouping(customer) = 1 then 'TOTAL' else customer end, sum(amount)
from invoices
group by customer with rollup
transact-sql :
https://technet.microsoft.com/en-US/library/ms189305%28v=sql.90%29.aspx
select customer, sum(amount)
from invoices
group by customer with rollup
this will give null for the total
if you want the word "total":
select case when grouping(customer) = 1 then 'TOTAL' else customer end, sum(amount)
from invoices
group by customer with rollup
ASKER
I have found an alternative on the internet in the meantime.
Allows very complex filtering, sorting, grouping... Sometimes I create temporary table in procedure and do very complex calculations.