X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8A7B553CA3 for ; Fri, 8 Apr 2005 14:29:14 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12840-07 for ; Fri, 8 Apr 2005 13:29:10 +0000 (GMT) Received: from vms040pub.verizon.net (vms040pub.verizon.net [206.46.252.40]) by svr1.postgresql.org (Postfix) with ESMTP id 8CF9553CBB for ; Fri, 8 Apr 2005 14:29:09 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms040.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEM00HQRQSOQQQ6@vms040.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 08:29:13 -0500 (CDT) Date: Fri, 08 Apr 2005 09:29:13 -0400 From: "Joel Fradkin" Subject: getting count for a specific querry To: Message-id: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: multipart/alternative; boundary="----=_NextPart_000_0001_01C53C1D.6DE41730" Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.382 tagged_above=0 required=5 tests=AWL, HTML_60_70, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200504/86 X-Sequence-Number: 20979 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C53C1D.6DE41730 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Per a thread a while back the discussion was along the lines of serving = data up to the web quick. Our app currently pulls a bunch of data to several query pages. I have tried and not gotten the queries to return as fast as they do now which is a huge disappointment as the hardware is twice as powerful as = our current production. I can get it pretty close on some thing but others = are just plain slower. =20 My idea is to use the limit and offset to return just the first 50 = records, if they hit next I can set the offset. My understanding was this gets slower as you move further into the data, = but we have several options to modify the search, and I do not believe our clients will page very far intro a dataset. =20 One problem I think I will have though is they currently have count of = the records matching their request and I would like to keep that as a = display field =20 So given a table of associates my default query will be something like Select * from tblassoc where clientnum =3D 'WAZ' and isdeleted is false The user could add stuff like and where first name like 'Joel' =20 Currently it returns all records with a count and a display of the = records your viewing like 1-50 of 470, next page is 51-100 etc. Is there a fast way to get the count? Will this concept fly? Also I am getting heat that my search is now case sensitive. What is the best way = to get a case insensitive search? I could use ~* or perhaps do an UPPER(firstname) in the select etc?=20 =20 Thanks for any ideas here. I have tried playing with various settings = and have not seen my times change much, I will persue this on the = performance mailing list. =20 =20 =20 Joel Fradkin =20 =20 =20 ------=_NextPart_000_0001_01C53C1D.6DE41730 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Per a thread a while back the discussion was along = the lines of serving data up to the web quick.

Our app currently pulls a bunch of data to several = query pages.

I have tried and not gotten the queries to return as = fast as they do now which is a huge disappointment as the hardware is twice as = powerful as our current production. I can get it pretty close on some thing but = others are just plain slower.

 

My idea is to use the limit and offset to return just = the first 50 records, if they hit next I can set the = offset.

My understanding was this gets slower as you move = further into the data, but we have several options to modify the search, and I = do not believe our clients will page very far intro a = dataset.

 

One problem I think I will have though is they = currently have count of the records matching their request and I would like to = keep that as a display field

 

So given a table of associates my default query will = be something like

Select * from tblassoc where clientnum =3D = ‘WAZ’ and isdeleted is false

The user could add stuff like and where first name = like ‘Joel’

 

Currently it returns all records with a count and a = display of the records your viewing like 1-50 of 470, next page is 51-100 = etc.

Is there a fast way to get the count? Will this = concept fly? Also I am getting heat that my search is now case sensitive. What is the = best way to get a case insensitive search? I could use ~* or perhaps do an = UPPER(firstname) in the select etc?

 

Thanks for any ideas here. I have tried playing with = various settings and have not seen my times change much, I will persue this on = the performance mailing list.

 

 

 

Joel Fradkin

 


 

 

------=_NextPart_000_0001_01C53C1D.6DE41730--