Search results

I have a search page on my web application that searches a database with
1000's of rows. The search results are returned in like 45-50 seconds.
If the user goes to another page and then returns to the same page again
it takes the same amount of time.

Is there a way to use session variables to remember the search results
and if user comes back to the page with the same criteria it shows the
static results and not take as long.

Help is needed urgent. Thanks

Regards,
Harpreet Kaur

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search results

I think before trying this you should take a closer look at your
database and query structure to determine what is causing a 45 to 50
second delay in getting the results. It may be that you just need to
optimize a bit. For instance, if you have a large number of columns and
are using select *, perhaps instead only select the columns you actually
need to display.

One of my web apps for part number searches takes a user's input, finds
all matching part numbers in Table A which has 42,000 rows, then takes
all of those Table A results and finds all the corresponding part
numbers in Table B that has 115,000 rows. This takes 3 seconds or less
depending on the number of results.

Some first things to check are how many rows are there in the table? You
say 1000's.. but even 100,000 really isn't all that many. How many
results are being returned on a typical user's query? Cheers.

Armando

Harpreet wrote:
> I have a search page on my web application that searches a database with
> 1000's of rows. The search results are returned in like 45-50 seconds.
> If the user goes to another page and then returns to the same page again
> it takes the same amount of time.
>
> Is there a way to use session variables to remember the search results
> and if user comes back to the page with the same criteria it shows the
> static results and not take as long.
>
> Help is needed urgent. Thanks
>
> Regards,
> Harpreet Kaur
>

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Search results

Hi!

I agree to Armando about the optimasation about the search-query. If you sh=
ow some bit of code (it may be useful only with the actual query used to se=
arch), we maybe can help you!

Best regards
/Gustav Wiberg
HMN Konsult (Sweden)
http://www.hmn.se/

=20

-----Original Message-----
From: Harpreet [mailto:harpreet@crispincorp.com]=20
Sent: Thursday, February 01, 2007 10:22 PM
To: php windows
Subject: [PHP-WIN] Search results


I have a search page on my web application that searches a database with
1000's of rows. The search results are returned in like 45-50 seconds.
If the user goes to another page and then returns to the same page again
it takes the same amount of time.

Is there a way to use session variables to remember the search results
and if user comes back to the page with the same criteria it shows the
static results and not take as long.

Help is needed urgent. Thanks

Regards,
Harpreet Kaur

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Search results

SQL looks as below:

select top 400 * from na_item_view_new where ((story_name like
'%Midlothian%' ) or (title like '%Midlothian%' ) or (script like
'%Midlothian%' ) or (item_text like '%Midlothian%' ) or (keyword like
'%Midlothian%' ) or (notes like '%Midlothian%' ) or (description like
'%Midlothian%' ) or (clipname like '%Midlothian%' ) or (creator like
'%Midlothian%' )) and Convert(datetime,rundown_date) >=3D '05/02/2006' =
and
Convert(datetime,rundown_date) <=3D '02/02/2007' and type=3D1 and
content_status=3D1 order by convert(datetime, rundown_date) asc


View is as below:
CREATE view na_item_view_new as
select s.story_id,
u.title as title,
s.title as story_name,
u.state,
[rundown_date]=3Dconvert(char(10),u.air_date,101),
'' as video,
'' as cg_text,
SUBSTRING(s.text, 1, 500) AS script,
SUBSTRING(i.text, 1, 500) as item_text ,
i.type,
i.content_status,
k.keyword,
i.editorial_description as description ,
d.description as notes,
s.editor as creator,
i.original_material_id as clipname,
i.ar_material_id as material_id
from=20
(select null as state,null as type, rundown_id, ncs_rundown_id,
edit_duration, title,=20
[air_date]=3Dconvert(char(10),air_date, 101), =20
edit_start_time=3Dsubstring(convert(varchar(10),=20
edit_start_time, 114),1,8) from na_rundown_tbl where rundown_id not in
(select ref1 from req_state_tbl where type=3D(select type_id from
req_type_tbl where type=3D'NA_ST_RUNDOWN'))
union
select state, type, rundown_id, ncs_rundown_id, edit_duration, title,
[air_date]=3Dconvert(char(10),air_date, 101), =20
edit_start_time=3Dsubstring(convert(varchar(10),=20
edit_start_time, 114),1,8) from na_rundown_tbl r left outer join
req_state_tbl s on r.rundown_id=3Ds.ref1
where (state=3D5 AND type=3D(select type_id from req_type_tbl where
type=3D'NA_ST_RUNDOWN'))) u
inner join na_story_tbl s on s.rundown_id=3Du.rundown_id=20
left outer join na_item_tbl i on s.story_id=3Di.story_id
left outer join na_itemkeyword_tbl k on i.item_id=3Dk.item_id
left outer join na_itemdesc_tbl d on i.item_id=3Dd.item_id


Regards,
Harpreet Kaur
Crispin Corporation=20
(919) 367-7911
(919) 791-9886 (Cell)

-----Original Message-----
From: Gustav Wiberg [mailto:gustav@hmn.se]=20
Sent: Friday, February 02, 2007 1:47 AM
To: Harpreet; 'php windows'
Subject: RE: [PHP-WIN] Search results

Hi!

I agree to Armando about the optimasation about the search-query. If you
show some bit of code (it may be useful only with the actual query used
to search), we maybe can help you!

Best regards
/Gustav Wiberg
HMN Konsult (Sweden)
http://www.hmn.se/

=20

-----Original Message-----
From: Harpreet [mailto:harpreet@crispincorp.com]=20
Sent: Thursday, February 01, 2007 10:22 PM
To: php windows
Subject: [PHP-WIN] Search results


I have a search page on my web application that searches a database with
1000's of rows. The search results are returned in like 45-50 seconds.
If the user goes to another page and then returns to the same page again
it takes the same amount of time.

Is there a way to use session variables to remember the search results
and if user comes back to the page with the same criteria it shows the
static results and not take as long.

Help is needed urgent. Thanks

Regards,
Harpreet Kaur

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Search results

I see what you mean now, even 400 results would take a long time doing
like matching on this many text fields without fulltext indexing. In
this case, you want to investigate the use of PHP's serialize() and
unserialize() functions. Check out this article on Zend Developer Zone
for information:

http://devzone.zend.com/node/view/id/1258

If your requirement is user-specific then you'll need to determine how
you want to accomplish that but that shouldn't be too difficult. The
above page uses MySQL as it's example but since you're doing the caching
via PHP then it shouldn't be an issue. HTH. Cheers.

Armando

..

Harpreet wrote:
> SQL looks as below:
>
> select top 400 * from na_item_view_new where ((story_name like
> '%Midlothian%' ) or (title like '%Midlothian%' ) or (script like
> '%Midlothian%' ) or (item_text like '%Midlothian%' ) or (keyword like
> '%Midlothian%' ) or (notes like '%Midlothian%' ) or (description like
> '%Midlothian%' ) or (clipname like '%Midlothian%' ) or (creator like
> '%Midlothian%' )) and Convert(datetime,rundown_date) >= '05/02/2006' and
> Convert(datetime,rundown_date) <= '02/02/2007' and type=1 and
> content_status=1 order by convert(datetime, rundown_date) asc
>
>
> View is as below:
> CREATE view na_item_view_new as
> select s.story_id,
> u.title as title,
> s.title as story_name,
> u.state,
> [rundown_date]=convert(char(10),u.air_date,101),
> '' as video,
> '' as cg_text,
> SUBSTRING(s.text, 1, 500) AS script,
> SUBSTRING(i.text, 1, 500) as item_text ,
> i.type,
> i.content_status,
> k.keyword,
> i.editorial_description as description ,
> d.description as notes,
> s.editor as creator,
> i.original_material_id as clipname,
> i.ar_material_id as material_id
> from
> (select null as state,null as type, rundown_id, ncs_rundown_id,
> edit_duration, title,
> [air_date]=convert(char(10),air_date, 101),
> edit_start_time=substring(convert(varchar(10),
> edit_start_time, 114),1,8) from na_rundown_tbl where rundown_id not in
> (select ref1 from req_state_tbl where type=(select type_id from
> req_type_tbl where type='NA_ST_RUNDOWN'))
> union
> select state, type, rundown_id, ncs_rundown_id, edit_duration, title,
> [air_date]=convert(char(10),air_date, 101),
> edit_start_time=substring(convert(varchar(10),
> edit_start_time, 114),1,8) from na_rundown_tbl r left outer join
> req_state_tbl s on r.rundown_id=s.ref1
> where (state=5 AND type=(select type_id from req_type_tbl where
> type='NA_ST_RUNDOWN'))) u
> inner join na_story_tbl s on s.rundown_id=u.rundown_id
> left outer join na_item_tbl i on s.story_id=i.story_id
> left outer join na_itemkeyword_tbl k on i.item_id=k.item_id
> left outer join na_itemdesc_tbl d on i.item_id=d.item_id
>
>
> Regards,
> Harpreet Kaur
> Crispin Corporation
> (919) 367-7911
> (919) 791-9886 (Cell)
>
> -----Original Message-----
> From: Gustav Wiberg [mailto:gustav@hmn.se]
> Sent: Friday, February 02, 2007 1:47 AM
> To: Harpreet; 'php windows'
> Subject: RE: [PHP-WIN] Search results
>
> Hi!
>
> I agree to Armando about the optimasation about the search-query. If you
> show some bit of code (it may be useful only with the actual query used
> to search), we maybe can help you!
>
> Best regards
> /Gustav Wiberg
> HMN Konsult (Sweden)
> http://www.hmn.se/
>
>
>
> -----Original Message-----
> From: Harpreet [mailto:harpreet@crispincorp.com]
> Sent: Thursday, February 01, 2007 10:22 PM
> To: php windows
> Subject: [PHP-WIN] Search results
>
>
> I have a search page on my web application that searches a database with
> 1000's of rows. The search results are returned in like 45-50 seconds.
> If the user goes to another page and then returns to the same page again
> it takes the same amount of time.
>
> Is there a way to use session variables to remember the search results
> and if user comes back to the page with the same criteria it shows the
> static results and not take as long.
>
> Help is needed urgent. Thanks
>
> Regards,
> Harpreet Kaur
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php