Re: [SQL] SQL stored proc query (optimising)
[EMAIL PROTECTED] writes: > I'm currently developing a database in which I have a list of > drivers. I've written a stored procedure for MS SQL Server This list is not for discussing MS SQL. Please direct your question to a more approriate forum. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Help with LIKE
I have a table with 8,628,633 rows that I'd LIKE to search (ha ha). I have a very simple query: SELECT * FROM tableA WHERE column1 LIKE '%something%'; tableA.column1 has an index on it and the database has been vacuumed recently. My problem is with the output of EXPLAIN: ++ | QUERY PLAN | ++ | Seq Scan on tableA (cost=0.00..212651.61 rows=13802 width=46) | | Filter: (column1 ~~ '%something%'::text) | ++ I don't like that cost (2,12,651) at all! Is there anyway I can optimize this query? Make a different kind of index (it's currently btree)? Use substr or indexof or something instead of LIKE? Thoughts? -- David Olbersen iGuard Engineer 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] What this parser mean?
I wrote a java program to query a data from 4 tables and the string query is like: querydata ="select bizfavorite.bizid,bizprofile.bizname,bizprofile.bizphone,bizprofile.bizfax,bizfavorite.searchtype," + "bizfavorite.keyword,to_char(bizfavorite.listdate,'DD-MM-') as listdate,bizfavorite.offerid,offer.otype,offer.bizid as obizid," + "to_char(offer.oposted_date,'DD-MM-') as oposted_date,to_char(offer.oexpiry_date,'DD-MM-') as oexpiry_date," + "userprofile.username,userprofile.ufullname,userprofile.uemail" + " from bizfavorite join bizprofile using(bizid) join userprofile using(bizid) left join offer using(offerid)" + " where bizfavorite.username= ? and urole='1' order by listdate desc" + " limit " + recordPerpage + "," + beginRecord; When I run this query, I got this message : Message: ERROR: parser: parse error at or near "-" but when I ran this query from psql command prompt its ok.I got what I want. b2bscm=> select bizprofile.bizname,bizprofile.bizphone,bizprofile.bizfax,bizfavorite.searchtype,bizfavorite.keyword,to_char(bizfavorite.listdate,'DD-MM-') as listdate,bizfavorite.offerid,offer.otype,offer.bizid as obizid,to_char(offer.oposted_date,'DD-MM-') as date,to_char(offer.oexpiry_date,'DD-MM-') as oexpiry_date,userprofile.username,userprofile.ufullname,userprofile.uemail from bizfavorite join bizprofile using(bizid) join userprofile using(bizid) left join offer using(offerid) where bizfavorite.username= 'faiz' and urole='1' order by listdate desc limit 8,0; Any help, very much appreciated..Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What this parser mean?
Hi Abdul, You may have lost the '' around the date specification in the to_char function when forming the string in Java. Before submitting the query, check the contents of the query string to make sure you still have the '' marks. Regards, David Witham -Original Message- From: Abdul Wahab Dahalan [mailto:[EMAIL PROTECTED] Sent: Friday, 21 March 2003 13:47 To: [EMAIL PROTECTED] Subject: [SQL] What this parser mean? I wrote a java program to query a data from 4 tables and the string query is like: querydata ="select bizfavorite.bizid,bizprofile.bizname,bizprofile.bizphone,bizprofile.bizfax,bizfavorite.searchtype," + "bizfavorite.keyword,to_char(bizfavorite.listdate,'DD-MM-') as listdate,bizfavorite.offerid,offer.otype,offer.bizid as obizid," + "to_char(offer.oposted_date,'DD-MM-') as oposted_date,to_char(offer.oexpiry_date,'DD-MM-') as oexpiry_date," + "userprofile.username,userprofile.ufullname,userprofile.uemail" + " from bizfavorite join bizprofile using(bizid) join userprofile using(bizid) left join offer using(offerid)" + " where bizfavorite.username= ? and urole='1' order by listdate desc" + " limit " + recordPerpage + "," + beginRecord; When I run this query, I got this message : Message: ERROR: parser: parse error at or near "-" but when I ran this query from psql command prompt its ok.I got what I want. b2bscm=> select bizprofile.bizname,bizprofile.bizphone,bizprofile.bizfax,bizfavorite.searchtype,bizfavorite.keyword,to_char(bizfavorite.listdate,'DD-MM-') as listdate,bizfavorite.offerid,offer.otype,offer.bizid as obizid,to_char(offer.oposted_date,'DD-MM-') as date,to_char(offer.oexpiry_date,'DD-MM-') as oexpiry_date,userprofile.username,userprofile.ufullname,userprofile.uemail from bizfavorite join bizprofile using(bizid) join userprofile using(bizid) left join offer using(offerid) where bizfavorite.username= 'faiz' and urole='1' order by listdate desc limit 8,0; Any help, very much appreciated..Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
