group by issue

Alright, so I'm trying to bind a list of pictures to a repeater but the sql I'm trying to write is killing me. The goal is simple: Write a query that returns all the pictures where the selected people are tagged but only return those pictures where all selected people are in them. So far I have the following SQL statement.

SELECT picture.id, picture.name, picture.album_id,
tag.id , tag.picture_id, tag.people_id
FROM picture LEFT OUTER JOIN
tag ON picture.id = tag.picture_id
WHERE (tag.people_id = '1') OR
(tag.people_id = '3')
GROUP BY picture.id
HAVING (COUNT(picture.id) > 1)

Now the problem is that you have to include all the columns that appear in the SELECT statement in the GROUP BY clause otherwise it errors out (this query will do that) but if I include them all then I get zero results when I know I should have 1 result given this particular query's desired result. Now when I was working with mySQL you could leave out columns in the GROUP BY and mySQL would handle it but seems SQL Server is not so forgiving.

Any ideas?

Without seeing the data, etc, it's hard to say for sure, but I suspect that the group by is not your problem (and btw, a group by without including the group by fields in the select list makes absolutely no sense, even though I know some databases may implicitely add the columsn for you -- not sure how mySQL handles it). I suspect you may be running into a subtle problem with the new ansi syntax for outer joins (maybe I'm wrong) when you include a test on the outer table. Seehttp://www.databasejournal.com/features/mssql/article.php/1438001 and scroll down to "outer join gotchas"

|||

I think I have the outer join right...see below

SELECT picture.name, picture.id, folders.pictures + picture.name AS filename
FROM folders CROSS JOIN
picture LEFT OUTER JOIN
tag ON picture.id = tag.picture_id
WHERE (tag.people_id = '1') OR
(tag.people_id = '3')

Produces (just test data):

nameidfilenameCopy of Copy of Copy of IMG_0052.jpg90~\Pictures\Copy of Copy of Copy of IMG_0052.jpgCopy of Copy of Copy of IMG_0052.jpg90~\Pictures\Copy of Copy of Copy of IMG_0052.jpgCopy of Copy of Copy of IMG_0053.jpg91~\Pictures\Copy of Copy of Copy of IMG_0053.jpgCopy of Copy of Copy of IMG_0054.jpg92~\Pictures\Copy of Copy of Copy of IMG_0054.jpg

Now this produces an output of 4 results because person 1 & 3 are both tagged in two pictures with one of those pictures containing both people. Now what I need to do is tweak the query so that it only returns that one record, the one that is duplicated. And now that I've typed this all out something has come to me so I tried the following query.

SELECT picture.name, picture.id, folders.pictures + picture.name AS filename
FROM folders CROSS JOIN
picture LEFT OUTER JOIN
tag ON picture.id = tag.picture_id
WHERE (tag.people_id = '1') OR
(tag.people_id = '3')
GROUP BY picture.name, picture.id, folders.pictures, picture.name
HAVING (COUNT(picture.id) > 1)

And that did it. Finally got the number that I was looking for. And the below query produces the count on the previous page to notify the user how many results the above query will return when they click search

SELECT COUNT(DISTINCT picture.name) FROM picture left OUTER JOIN tag ON picture.id = tag.picture_id where ( people_id = '1' or people_id = '3' ) GROUP BY picture.name HAVING (COUNT(picture.name) > 1)

Thanks for making me think a little more into it. Sometimes you get so stuck in one way of thinking it takes something else to get you to think about it from a different perspective, plus the fact I always have trouble with group by clauses

Group By clause killing performance

I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.

Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.

Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.

If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?

Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?

I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.

It is a combination of few factors.(1) It is imptant to have proper indexes on columns being queried in WHERE and GROUP BY (2) Also see if you can move the GROUP BY to the reporting tool? (3) Is it the GROUP BY thats killing it or something else? Are you using any functions on columns in the WHERE? like some CONVERT(Datecolumn,100) >= '2007/01/01' etc?

|||

It is definetly the group by that is killing it. When the query was rewritten to to remove the group by the execution time dropped through the floor.

from 1 minute to less than 1 second.

Here is a sudo example of what I mean

old query first

SELECT
column1,
column2,
column3,
SUM(something)
FROM
table1
inner join table 2 on 1.columna = 2.columnb
GROUP BY
column1,
column2,
column3

new query

SELECT
column1,
column2,
column3,
(SELECT SUM(something) From sometable) AS 'blah'
FROM
table1
inner join table 2 on 1.columna = 2.columnb

I know that there huge gap between what is really going on and the code above but you get the main idea. moving the sum to a select so that the group by is no longer required. This and this allow drastically reduced the amount of time that it took to get the data. I knew that group by was expensive I just didn't realize how expensive it was.

|||

The group by clause is not in itself a bad performer. There is something else at work but without more detail, I can't tell you what.

The two queries you gave aren't the same thing. The second query doesn't do a sum based on the contents of the current row (No where clause relating the two). Which then of course it runs much faster, it's only executing the sum once, and using it on every row of the outer query.

|||

No mystery. "If I cannot do a reasonable query of a couple of million records..."

Sorting a couple of million records is, well, expensive! That's what a group by does, it sorts. And you don't even have a where clause to limit the answer set.

If you put a clustered index on column1, column2, column3 it will be able to avoid the sort, but you need to look at that carefully since it may have an impact on other queries (and you may already have a clustered index)

|||

True, the second query is unsorted (Not that common to request a set of data and not care about it's sort order), which will obviously be a completely different query plan. I would venture to guess that you don't have a good index on the table either that can/will help you.

Instead of putting a clustered index on the table, if you put an index on column1,column2,column3 and the field you are summing, your query time will drop significantly as well.

Faster yet, would be to use an indexed view.

|||

I am hearing basically what I thought I would hear. GROUP BY equals SORTING, a couple million records is a lot of data, no need to aviod GROUP BY like the plauge, check the indexes and statistics too.

Thanks. There is never a right or wrong answer to this kind of thing, it always depends on the shop and the database.

GridView delete function

This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource:

 <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString%>" SelectCommand="SELECT * FROM [Index] WHERE (Type LIKE '%' + @.SearchText2 + '%') OR (Product LIKE '%' + @.SearchText2 + '%') OR (Version LIKE '%' + @.SearchText2 + '%') OR (Binder LIKE '%' + @.SearchText2 + '%') OR (Language LIKE '%' + @.SearchText2 + '%') OR (CDName LIKE '%' + @.SearchText2 + '%') OR (Details LIKE '%' + @.SearchText2 + '%') OR (ISOLink LIKE '%' + @.SearchText2 + '%')" DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.original_ID" UpdateCommand="UPDATE [Index] SET Type = @.Type, Product = @.Product , Version = @.Version, Binder = @.Binder, Language = @.Language, CDName = @.CDName, Details = @.Details, ISOLink = @.ISOLink WHERE ID = @.ID"> <SelectParameters> <asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Type" /> <asp:Parameter Name="Product" /> <asp:Parameter Name="Version" /> <asp:Parameter Name="Binder" /> <asp:Parameter Name="Language" /> <asp:Parameter Name="CDName" /> <asp:Parameter Name="Details" /> <asp:Parameter Name="ISOLink" /> </UpdateParameters> </asp:SqlDataSource>

It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@.ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc isdifferent from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.

-Brandan

Hello

What if you add a semicolumn after @.original_ID ? like this"DELETE FROM [Index] WHERE [ID] = @.original_ID;"

|||

Is the ID column your table's primary key? If yes, you need to make sure that the ID is set in your GridView's DadaKeyNames and you should change your DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> and DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.ID"

If you cannot make this work, please post your GridView part here and if you can list all your columns' name instead of a * in your SELECT statement, that would be great. Thanks.

|||

If it doesn't fixes it it might just be the DataKeyNames field of the gridview that need to be set to ID .

|||

**RESOLVED**

It was definitely the DataKeyNames. I had recreated the gridview so many times I forgot to put it back in. thanks ya'll.