none
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. RRS feed

  • Question

  • Hi Dear,
    I have some problem.

    Problem Is:
    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.


    I have two table.
    1. BlogUrl
    2.HistoryBlogs

    table BlogUrl has approximate 30 url(some part of url).
    and table HistoryBlogs has approximate 2,00,000(2 lac) record.

    table history blog has a colon blogUrl and a colon subject name.

    when a user select one url from blogUrl table then display all subject related to blogUrl.
    it's ok.
    but i want select all blogUrl related subject.

    my query is:
    SSELECT distinct S.subject_id as 'id', cast(S.subject_raw as varchar(8000)) as 'subject' FROM
    HistoryBlogs S where
                        substring
                        (
                         cast(S.blog_url as varchar(8000)),
                         charindex('/',cast(S.blog_url as varchar(8000)))+2,
                         charindex('/',
                             substring
                                (
                                  cast(S.blog_url as varchar(8000)),
                                      charindex('/',cast(S.blog_url as varchar(8000)))+2,
                                  len(cast(S.blog_url as varchar(8000)
                                        )
                                  )
                              )
                              )-1
                        )
                        IN
                            ('-' , 'discussions.apple.com' , 'feeds.gawker.com' , 'legalalan.blogspot.com' , 'real.estatez.net' , 'szeteng.blogspot.com' , 'willdo.philadelphiaweekly.com' , 'www.beginnertriathlete.com' , 'www.bestbuy.ca' , 'www.bestbuy.com' , 'www.binsearch.info' , 'www.centennialcollege.ca' , 'www.chatsusa.com' , 'www.dvinfo.net' , 'www.feedhub.com' , 'www.futureshop.ca' , 'www.libble.com' , 'www.mcdonalds.com' , 'www.monova.org' , 'www.net-security.org' , 'www.osnews.com' , 'www.senecac.on.ca' , 'www.shop.com' , 'www.sumotorrent.com' , 'www.theimpulsivebuy.com' , 'www.tomshardware.com' , 'www.wal-mart.ca' , 'www.walmart.com' , 'www.xbitlabs.com') group by S.Subject_Id,cast(S.Subject_Raw as varchar(8000))


    This query is ok.
    but when we want select all blogUrl.

    then i found above mention error

    i have asp.net 2.0 and sql server 2000.
    this type problem i am facing on remote server

    Please help me.


    Yogesh Saini
    Friday, March 28, 2008 12:58 PM

All replies

  • Get rid of the ridiculously long IN() clause, and put that list of URLs in a separate table where they belong (really-- It's database, tables are your friend).  Call it, say, "SearchURLs"

    Then the query becomes:


    SELECT distinct
                 B.subject_id as 'id',
                 cast(B.subject_raw as varchar(8000)) as 'subject'
                FROM HistoryBlogs B, SearchUrls S
    where    B.blog_url like '%'+S.Url +'%'

     


    You can probably speed it up even more by writing it as:


    SELECT distinct
                 B.subject_id as 'id',
                 cast(B.subject_raw as varchar(8000)) as 'subject'
                FROM HistoryBlogs B, SearchUrls S
    where    B.blog_url like 'http://'+S.Url +'%'

     


    Friday, March 28, 2008 3:05 PM
  • Hi Sir,


    Your advise is very halpfull.

    I have made a table searchblog with userid and url volon name.

    and use above mention your query.

    increase some performace.

    if we assign 7 or 8... to a particular  user.
    and user want to view all url related information  in history table.
    history table has all url related to serch table.


    For Example:

    Search table url:http://abc.com
    and historytable have url:
    URL                                            subjectId         Subject_row
    http://abc.com/asd/                          1                  a
    http://abc.com/dfdf/fdf                        1                 a
    http://abc.com/test/                           2                 b
    http://abc.com/fdf/dfd.htm                  3                  c
    http://abc.com/dsdfdsf/                      3                  c
    http://abc.com/dftrtret/                       4                  s
    http://abc.com/arrtre/rtret/rtretr/ertert.php  4            s

    .......................................................
    .......................................................
    .......................................................

    histroy table has approximate 2lac record.

    i want find subject row according to the search url.
    i want all search url related subject row.


    My Query:


    SELECT distinct S.subject_id as 'id', cast(S.subject_raw as varchar(8000)) as 'subject'
     FROM STATEMENT S,Custom_Blogs B INNER JOIN onsurvey.member_custom_blogs M ON B.Blogs_ID=M.Blogs_Id where M.MemberId=110 and cast(S.blog_url as varchar(8000))
     like '%'+ cast(B.Url as varchar(8000)) COLLATE SQL_Latin1_General_CP1_CI_AS +'%' group by S.Subject_Id,cast(S.Subject_Raw as varchar(8000))


    if you have any solution please help me.


    Thanks

    yogesh saini



    Saturday, March 29, 2008 11:59 AM