Re: [SQL] slow query execution
Am I right in assuming that you're working on a VB application with a ListView AxtiveX Control in it? If so consider putting the value of the ID field of your table in the Tag Property of each item in your listview. By looping over all items in your listview you can then check the property for certain values and adjust other properties as required (eg highlight them). Alternatively if you're working with ado you can store the records bookmark in the tag property. That's particulary handy when you want to look up information in a recordset when clicking on an item in the listview. >>> Trigve Siver <[EMAIL PROTECTED]> 2007-05-30 21:11 >>> >- Original Message >From: Richard Huxton <[EMAIL PROTECTED]> >To: Trigve Siver <[EMAIL PROTECTED]> >Cc: [email protected] >Sent: Wednesday, May 30, 2007 9:05:09 PM >Subject: Re: [SQL] slow query execution > >Trigve Siver wrote: >> >> I seee...when new data will be added to table, then row numbers wouldn''t be >> valid anymore. >> >> Hmmm...looked like I need to find other solution. > >Try explaining what it is you're trying to achieve, and maybe someone >will be able to suggest a solution. I need to perform search on some query (which is already shown in Listview control) and highlight items that meet search criteria. thanks Trigve Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] slow query execution
Hi, thanks for reply No, I'm working with c++ and libpqxx (pgsql c++ binding). I'm using Win32 Listview control with LS_OWNERDATA style. I can use std::map to map row_number to ID field but then I must fetch all records from that table. This could be ineffective when table has about 10.000+ records and user want to view/search only first 100 records. thanks Trigve - Original Message From: Bart Degryse <[EMAIL PROTECTED]> To: [email protected] Sent: Thursday, May 31, 2007 9:24:04 AM Subject: Re: [SQL] slow query execution Am I right in assuming that you're working on a VB application with a ListView AxtiveX Control in it? If so consider putting the value of the ID field of your table in the Tag Property of each item in your listview. By looping over all items in your listview you can then check the property for certain values and adjust other properties as required (eg highlight them). Alternatively if you're working with ado you can store the records bookmark in the tag property. That's particulary handy when you want to look up information in a recordset when clicking on an item in the listview. >>> Trigve Siver <[EMAIL PROTECTED]> 2007-05-30 21:11 >>> >- Original Message >From: Richard Huxton <[EMAIL PROTECTED]> >To: Trigve Siver <[EMAIL PROTECTED]> >Cc: [email protected] >Sent: Wednesday, May 30, 2007 9:05:09 PM >Subject: Re: [SQL] slow query execution > >Trigve Siver wrote: >> >> I seee...when new data will be added to table, then row numbers wouldn''t be >> valid anymore. >> >> Hmmm...looked like I need to find other solution. > >Try explaining what it is you're trying to achieve, and maybe someone >will be able to suggest a solution. I need to perform search on some query (which is already shown in Listview control) and highlight items that meet search criteria. thanks Trigve Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/
[SQL] ASK about SQL
Dear all, HELP me PLZ I'm used this SQL command : "" SELECT ean FROM icitem WHERE ean in (4902715720005,4909411012151,4909411010164,4902715688718,4902715731605,4909411008864,4909411010188,4909411010126,4909411010140,490941101 2052,4902715720104,4909411010089,4909411010201,0,0) ; ; The FILE content : but the result is : ean --- 4902715720005 4902715720005 4909411012151 4902715720104 4909411010164 4902715688718 4902715688718 4902715731605 4902715731605 4909411008864 4909411008864 4909411010089 4909411010188 4909411010126 4909411010126 4909411010140 4909411010140 4909411010164 4909411012052 4909411010188 4902715720104 4909411010201 4909411010089 4909411012052 4909411010201 4909411012151 that is different RESULT with the input !!! what must I do if i dont want POSTGRES not ORDER my SELECT data THX's Kunjungi halaman depan Yahoo! Indonesia yang baru! http://id.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] ASK about SQL
Do you mean you want it to order it in the same order as you've listed them in the IN condition? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ali nas Sent: Thursday, 31 May 2007 17:57 To: [email protected] Subject: [SQL] ASK about SQL Dear all, HELP me PLZ. ... I'm used this SQL command : "" SELECT ean FROM icitem WHERE ean in (4902715720005,4909411012151,4909411010164,4902715688718,4902715731605,49094 11008864,4909411010188,4909411010126,4909411010140,490941101 2052,4902715720104,4909411010089,4909411010201,0,0) ; ; The FILE content : but the result is : ean --- 4902715720005 4902715720005 4909411012151 4902715720104 4909411010164 4902715688718 4902715688718 4902715731605 4902715731605 4909411008864 4909411008864 4909411010089 4909411010188 4909411010126 4909411010126 4909411010140 4909411010140 4909411010164 4909411012052 4909411010188 4902715720104 4909411010201 4909411010089 4909411012052 4909411010201 4909411012151 that is different RESULT with the input !!! what must I do if i dont want POSTGRES not ORDER my SELECT data THX's Kunjungi halaman depan Yahoo! Indonesia yang baru! http://id.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] slow query execution
Sorry, I don't know C++ enough to help you much. I also don't understand your problem well enough. >>> Trigve Siver <[EMAIL PROTECTED]> 2007-05-31 9:46 >>> Hi, thanks for reply No, I'm working with c++ and libpqxx (pgsql c++ binding). I'm using Win32 Listview control with LS_OWNERDATA style. I can use std::map to map row_number to ID field but then I must fetch all records from that table. This could be ineffectivewhen table has about 10.000+ records and user want to view/search only first 100 records. thanks Trigve
Re: [SQL] slow query execution
Thanks for reply,
This solution looks promising. I'll look at it and test it and let you know.
Thanks once more
Trigve
- Original Message
From: Richard Huxton <[EMAIL PROTECTED]>
To: Trigve Siver <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2007 10:33:40 AM
Subject: Re: [SQL] slow query execution
Trigve Siver wrote:
> Hi, thanks for reply
>
> No, I'm working with c++ and libpqxx (pgsql c++ binding). I'm using
> Win32 Listview control with LS_OWNERDATA style. I can use std::map to
> map row_number to ID field but then I must fetch all records from
> that table. This could be ineffective when table has about 10.000+
> records and user want to view/search only first 100 records.
So - you want something like:
The user runs a query ("all blue things") and that gives a list of
results. They can then filter those results further ("shape=round") and
you want to highlight those elements that match.
You either can't or don't want to filter in the application, rather you
would like to run this as two queries but need to match up results from
the second query with the first query (your list).
Suggestion:
For the first query, make sure you have the relevant primary key columns
in your query and do:
CREATE TEMPORARY TABLE my_results AS SELECT ...
Then, you can join against that table in the second query. The temporary
table will exist until you disconnect - see CREATE TABLE for details.
--
Richard Huxton
Archonet Ltd
Ready
for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/
---(end of broadcast)---
TIP 1: 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
Re: [SQL] ASK about SQL
Yes, you can order it the same as how you've listed in the IN condition, but you need to use the ORDER BY clause... Essentially you just need to pass the same list to both IN and ORDER BY but with some slightly different formatting... SELECT ean FROMicitem WHERE ean IN (4902715720005, 4909411012151, 4909411010164, 4902715688718, 4902715731605, 4909411008864, 4909411010188, 4909411010126, 4909411010140, 4909411012052, 4902715720104, 4909411010089, 4909411010201, 0) ORDER BYCASE WHEN ean = 4902715720005 THEN '1' WHEN ean = 4909411012151 THEN '2' WHEN ean = 4909411010164 THEN '3' WHEN ean = 4902715688718 THEN '4' WHEN ean = 4902715731605 THEN '5' WHEN ean = 4909411008864 THEN '6' WHEN ean = 4909411010188 THEN '7' WHEN ean = 4909411010126 THEN '8' WHEN ean = 4909411010140 THEN '9' WHEN ean = 4909411012052 THEN '10' WHEN ean = 4902715720104 THEN '11' WHEN ean = 4909411010089 THEN '12' WHEN ean = 4909411010201 THEN '13' WHEN ean = 0 THEN '14' ; -Original Message- From: ali nas [mailto:[EMAIL PROTECTED] Sent: Thursday, 31 May 2007 18:57 To: Phillip Smith Subject: Hal: [SQL] ASK about SQL YES, I don't care with with ORDER BY clause. beacuse i just want what the data INPUT as like that the data OUTPUT ,so INPUT listed = OUTPUT listed. so there isn't way in POSTGRES to do like this ?? - Pesan Asli Dari: Phillip Smith <[EMAIL PROTECTED]> Kepada: ali nas <[EMAIL PROTECTED]> Terkirim: Kamis, 31 Mei, 2007 3:44:12 Topik: RE: [SQL] ASK about SQL You will need to explicitly tell Postgres what order you want it in - unless you tell PG the order you want with an ORDER BY clause, it assumes you don't care. I can't recall 100% the exact syntax for the ORDER BY in your case, but someone else more with it than me at the moment will be able to... Hang tight! :) -Original Message- From: ali nas [mailto:[EMAIL PROTECTED] Sent: Thursday, 31 May 2007 18:31 To: Phillip Smith Subject: Hal: [SQL] ASK about SQL THX's for answered my question. YES, i want POSTGRES just do SELECT in the IN condition. add clue, POSTGRES will show the SELECT data which POSTGRES find first. I dont wanna like that. So what will I do ?? ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments Kunjungi halaman depan Yahoo! Indonesia yang baru! http://id.yahoo.com/ ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] slow query execution
Trigve Siver wrote:
- Original Message
From: Richard Huxton <[EMAIL PROTECTED]>
To: Trigve Siver <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2007 10:33:40 AM
Subject: Re: [SQL] slow query execution
So - you want something like:
The user runs a query ("all blue things") and that gives a list of
results. They can then filter those results further ("shape=round") and
you want to highlight those elements that match.
You either can't or don't want to filter in the application, rather you
would like to run this as two queries but need to match up results from
the second query with the first query (your list).
Suggestion:
For the first query, make sure you have the relevant primary key columns
in your query and do:
CREATE TEMPORARY TABLE my_results AS SELECT ...
Then, you can join against that table in the second query. The temporary
table will exist until you disconnect - see CREATE TABLE for details.
I think that I can use temporary tables with my previous soultion. As you
mentioned,
I can create temp table with the select ("all blue things")[main select]. (I think I can also add row_numbers
to each record as I want to jump to first record in my list which satisfy ("shape=round")
condition) When ("shape=round") Query will be made and the:
a)I can make join in this the query with my temp table (as you mentioned)
b)I can make this query on temp table. But the temporary table haven't indexes.
You can add indexes if you want them. You'll also want to run analyze
against the temp table.
But when main select is some complicated select then (b) can be used.
thanks
Trigve
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
