Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 SQL Error, Select would take too long...?

Author  Topic 

PC-fre@k
Starting Member

5 Posts

Posted - 2004-04-14 : 12:11:12
Hi all, found this site and I hope one of you could help me to solve the following problem:

The SELECT would examine too many records and probably
take a very long time. Check your WHERE and use SET OPTION
SQL_BIG_SELECTS=1 if the SELECT is ok


The query:

$qry=$DB->sql_qry("SELECT p.*,COUNT(c.id) AS num,k.kategorie
AS katname FROM ".$this->sql_table["pics"]." AS p LEFT JOIN ".$this-
>sql_table['comments']." AS c ON p.id = c.pid LEFT JOIN ".$this-
>sql_table['kategorie']." AS k ON p.kategorie = k.id $this->katadd
GROUP BY p.id");


(part of the PHP)
It is a photo script, it shows photos/hits/comments and if they are
all together more then 4500 the error will show up. Íf I make sure
it is below that, no problems at all. I have a Forum running with
13000 posts so I think it isn't my database/server..

How should I set that option? Add somthing? I tried to find this
solution on google without succes. I hope I'm on the right place now!

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 12:39:42
You doing SQL Server? What platform?

1st I would suggest this be done in a stored procedure, but never mind that

How come you don't have a predicate?

Your results will be ALL rows...

Also, never do SELECT *

Look up Paging here...but you should provide something to redeuce the result set to what they want..

For example, you say you'be got 4500 rows...

So what, I have to page through to the row I want? Which happens to be 4234?

I'm not sticking around that long...



Brett

8-)
Go to Top of Page

PC-fre@k
Starting Member

5 Posts

Posted - 2004-04-14 : 12:59:36
Well it is a PHP script made by someone else..

Picture Solution, www.script-solution.de

I asked him how to fix it, but he even doesn't know about the error.
I'm a total SQL N00B, I can fix some php or combine scripts thats all...

It is on my webserver, System Debian GNU/Linux - Bi PIII 1Ghz - 1 Gb RAM - Kernel 2.4.x

[url]http://pc-freak.pc-freakz.com/DBinfo.txt[/url]

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-14 : 13:03:06
I believe that there is only one PHP person who frequents SQLTeam. Just so that you are aware, this site is for SQL Server, so your question may go unanswered here. I'm not sure if there is a PHP forum out there.

Brett, does dbforums.com have one?

Tara
Go to Top of Page

PC-fre@k
Starting Member

5 Posts

Posted - 2004-04-14 : 13:05:26
Ah, wrong place...

most ppl I ask dunno about this error, it seemed professional
out here I thought Just give it a try :P
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-14 : 13:08:57
Can you limit your result set by adding a WHERE clause to avoid the error? Are your tables properly indexed? This is where I would start troubleshooting.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 13:12:39
Sho 'nough

http://www.dbforums.com/f14/

Never been there...what the hell is PHP?



Brett

8-)
Go to Top of Page

PC-fre@k
Starting Member

5 Posts

Posted - 2004-04-14 : 13:22:32
Hehe, well I'll ask out there.

Thanks for the help so far and for the link!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 13:31:52
This is less of a technical problem, and more of an interface design problem....

Who's going to interogate 4500 lines of data?



Brett

8-)
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-15 : 19:57:04
quote:
Originally posted by X002548

Never been there...what the hell is PHP?

Wow, Brett, you're leading a sheltered life, aren't you? PHP is what all those Microsoft-haters use in place of ASP. PHP runs on Apache.

And by the way, I don't think was ever made completely clear, but the SQL_BIG_SELECTS OPTION is a mySQL setting.

And PC-fre@k, if you're still reading here, I second (or is it third?) the idea that there should probably be a WHERE clause in your SELECT statement unless you are really, really trying to analyze every row in the table. Oh, and I'd also be surprised if you can have all those other fields in the SELECT statement and then only GROUP BY the one field (p.id). In SQL Server, every field listed in the SELECT list has to either be included in the GROUP BY or have an aggregate function run on it.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]
Go to Top of Page

PC-fre@k
Starting Member

5 Posts

Posted - 2004-04-16 : 02:49:32
I'm stil reading here :D (I get emails)

I do not get it 100%, but I do think i get what you want to tell me.
it are not 4500 at once, all shoots are devived in parts of 300 or 400 that is what P.ID is in this query.

I will link this page to the owner of that script, he may get more out if it then I get :)

Maybe I will get some O'reily book ;) It is intresting...

Thanks
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-16 : 10:27:46
quote:
Originally posted by AjarnMark

quote:
Originally posted by X002548

Never been there...what the hell is PHP?

Wow, Brett, you're leading a sheltered life, aren't you?[/url]



It's lonely up here on the mainframe

And who wants to be a front end developer anyway...it should be all presentation layer....

All the code should be on the box....

What do I care if the control is blue or pink....

With that said...I just finished a phone call just about that, and figured I'm lacking and should probably set something up at home and start learning asp.net or something...

Maybe build a margarita site...wait, that's been done...

http://www.radiomargaritaville.com



Brett

8-)
Go to Top of Page
   

- Advertisement -