Loading ...
Sorry, an error occurred while loading the content.

Re: [mugh-sqlcon] Query Takes long time for execution !!!!

Expand Messages
  • vadlamani murthy
    Hi Rajendra, Before answering your question, I would like to know how often do you fire this query. Thanks Murthy Rajendra Prasad
    Message 1 of 6 , Apr 1, 2004
    • 0 Attachment
      Hi Rajendra,
       
      Before answering your question, I would like to know how often do you fire this query.
       
      Thanks
      Murthy

      Rajendra Prasad <rajendraprasad_t@...> wrote:
      Hi all,

      MUGH is doing grt.
      I have a table with around 1 crore records in that. Has fields
      {msgid,Subject..}, There is a cluster index on msgid column and now
      i want to make a search query to find all the ids with a speicific
      subject of my Interest. like:

      select msgid from <messagesTable>  where Subject like 'mySubject'


      But the query takes enormous time, Creating Index over the subject
      column (varchar(255)) is a good practice? since the number of
      transactions are also huge in a day (around 5000-6000 may be more
      also).

      can anybody suggest me a wayout for this scenario,

      Thanks in advance..
      Rajendra,





      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net

      http://www.mugh.net/sql




      Do you Yahoo!?
      Yahoo! Small Business $15K Web Design Giveaway - Enter today

    • Rajendra Prasad
      Hi Muthry, Thanks for your reply. The Query gets fired in a job which populates the tables from flat files. I need to link up the records to their related
      Message 2 of 6 , Apr 2, 2004
      • 0 Attachment
        Hi Muthry,

        Thanks for your reply.
        The Query gets fired in a job which populates the tables from flat
        files. I need to link up the records to their related ones, in few
        cases.
        When that condition happens then it fires this query to find the
        related message id with the incoming subject string.

        Thanks,
        Rajendra.

        --- In sqlcon@yahoogroups.com, vadlamani murthy
        <vadlamanimurth@y...> wrote:
        > Hi Rajendra,
        >
        > Before answering your question, I would like to know how often do
        you fire this query.
        >
        > Thanks
        > Murthy
        >
        > Rajendra Prasad <rajendraprasad_t@y...> wrote:
        > Hi all,
        >
        > MUGH is doing grt.
        > I have a table with around 1 crore records in that. Has fields
        > {msgid,Subject..}, There is a cluster index on msgid column and
        now
        > i want to make a search query to find all the ids with a speicific
        > subject of my Interest. like:
        >
        > select msgid from <messagesTable> where Subject like 'mySubject'
        >
        >
        > But the query takes enormous time, Creating Index over the subject
        > column (varchar(255)) is a good practice? since the number of
        > transactions are also huge in a day (around 5000-6000 may be more
        > also).
        >
        > can anybody suggest me a wayout for this scenario,
        >
        > Thanks in advance..
        > Rajendra,
        >
        >
        >
        >
        >
        > SqlCon is a special interests group run under the aegis of MUGH -
        http://www.mugh.net
        >
        > http://www.mugh.net/sql
        >
        >
        >
        > Yahoo! Groups SponsorADVERTISEMENT
        >
        >
        > ---------------------------------
        > Yahoo! Groups Links
        >
        > To visit your group on the web, go to:
        > http://groups.yahoo.com/group/sqlcon/
        >
        > To unsubscribe from this group, send an email to:
        > sqlcon-unsubscribe@yahoogroups.com
        >
        > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
        Service.
        >
        >
        >
        > ---------------------------------
        > Do you Yahoo!?
        > Yahoo! Small Business $15K Web Design Giveaway - Enter today
      • vadlamani murthy
        Hi Rajendra, This we cann t achieve at one go. You have to make use of all the options that you have and then you have to apply the best which fits. Please
        Message 3 of 6 , Apr 5, 2004
        • 0 Attachment
          Hi Rajendra,
          This we cann't achieve at one go.  You have to make use of all the options that you have and then you have to apply the best which fits.
           
          Please create a covered index on msgid and on subject.  The query should have order by clause on "Subject".  Get the execution plan for the query. 
          Based on the execution plan you will come to know whether the query is  making use of index or not and other details like I/O Cost,CPU Cost. 
           
          Gather the results and save the same.
           
          Now save the query as workload and run the index tuning wizard.  If it recommends any indexes go and build the same. 
           
          Again get the exeuction plan details. 
          Compare the execution plan details.
           
          Go for the better one. 
           
          You can also go for Full Text Search which is the other option that has struck my mind.  I am looking into it for various pros and cons.  I will give you more details in my next mail.
           
          Hope this will help you.
           
          Thanks
          Murthy 
           
           
           


          Rajendra Prasad <rajendraprasad_t@...> wrote:
          Hi Muthry,

          Thanks for your reply.
          The Query gets fired in a job which populates the tables from flat
          files. I need to link up the records to their related ones, in  few
          cases.
          When that condition happens then it fires this query to find the
          related message id with the incoming subject string.

          Thanks,
          Rajendra.

          --- In sqlcon@yahoogroups.com, vadlamani murthy
          <vadlamanimurth@y...> wrote:
          > Hi Rajendra,

          > Before answering your question, I would like to know how often do
          you fire this query.

          > Thanks
          > Murthy
          >
          > Rajendra Prasad <rajendraprasad_t@y...> wrote:
          > Hi all,
          >
          > MUGH is doing grt.
          > I have a table with around 1 crore records in that. Has fields
          > {msgid,Subject..}, There is a cluster index on msgid column and
          now
          > i want to make a search query to find all the ids with a speicific
          > subject of my Interest. like:
          >
          > select msgid from <messagesTable>  where Subject like 'mySubject'
          >
          >
          > But the query takes enormous time, Creating Index over the subject
          > column (varchar(255)) is a good practice? since the number of
          > transactions are also huge in a day (around 5000-6000 may be more
          > also).
          >
          > can anybody suggest me a wayout for this scenario,
          >
          > Thanks in advance..
          > Rajendra,
          >
          >
          >
          >
          >
          > SqlCon is a special interests group run under the aegis of MUGH -
          http://www.mugh.net
          >
          > http://www.mugh.net/sql
          >
          >
          >
          > Yahoo! Groups SponsorADVERTISEMENT
          >
          >
          > ---------------------------------
          > Yahoo! Groups Links
          >
          >    To visit your group on the web, go to:
          > http://groups.yahoo.com/group/sqlcon/
          >  
          >    To unsubscribe from this group, send an email to:
          > sqlcon-unsubscribe@yahoogroups.com
          >  
          >    Your use of Yahoo! Groups is subject to the Yahoo! Terms of
          Service.
          >
          >
          >
          > ---------------------------------
          > Do you Yahoo!?
          > Yahoo! Small Business $15K Web Design Giveaway - Enter today




          SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net

          http://www.mugh.net/sql



          Do you Yahoo!?
          Yahoo! Small Business $15K Web Design Giveaway - Enter today

        • Rajendra Prasad
          Hi Murthy, Thanks a lot. I would work on those lines and get back to u. Pls provide me your inputs also. Regards, Rajendra. ... options that you have and then
          Message 4 of 6 , Apr 7, 2004
          • 0 Attachment
            Hi Murthy,
            Thanks a lot.
            I would work on those lines and get back to u.
            Pls provide me your inputs also.

            Regards,
            Rajendra.

            --- In sqlcon@yahoogroups.com, vadlamani murthy
            <vadlamanimurth@y...> wrote:
            > Hi Rajendra,
            > This we cann't achieve at one go. You have to make use of all the
            options that you have and then you have to apply the best which fits.
            >
            > Please create a covered index on msgid and on subject. The query
            should have order by clause on "Subject". Get the execution plan
            for the query.
            > Based on the execution plan you will come to know whether the
            query is making use of index or not and other details like I/O
            Cost,CPU Cost.
            >
            > Gather the results and save the same.
            >
            > Now save the query as workload and run the index tuning wizard.
            If it recommends any indexes go and build the same.
            >
            > Again get the exeuction plan details.
            > Compare the execution plan details.
            >
            > Go for the better one.
            >
            > You can also go for Full Text Search which is the other option
            that has struck my mind. I am looking into it for various pros and
            cons. I will give you more details in my next mail.
            >
            > Hope this will help you.
            >
            > Thanks
            > Murthy
            >
            >
            >
            >
            >
            > Rajendra Prasad <rajendraprasad_t@y...> wrote:
            > Hi Muthry,
            >
            > Thanks for your reply.
            > The Query gets fired in a job which populates the tables from flat
            > files. I need to link up the records to their related ones, in
            few
            > cases.
            > When that condition happens then it fires this query to find the
            > related message id with the incoming subject string.
            >
            > Thanks,
            > Rajendra.
            >
            > --- In sqlcon@yahoogroups.com, vadlamani murthy
            > <vadlamanimurth@y...> wrote:
            > > Hi Rajendra,
            > >
            > > Before answering your question, I would like to know how often
            do
            > you fire this query.
            > >
            > > Thanks
            > > Murthy
            > >
            > > Rajendra Prasad <rajendraprasad_t@y...> wrote:
            > > Hi all,
            > >
            > > MUGH is doing grt.
            > > I have a table with around 1 crore records in that. Has fields
            > > {msgid,Subject..}, There is a cluster index on msgid column and
            > now
            > > i want to make a search query to find all the ids with a
            speicific
            > > subject of my Interest. like:
            > >
            > > select msgid from <messagesTable> where Subject like 'mySubject'
            > >
            > >
            > > But the query takes enormous time, Creating Index over the
            subject
            > > column (varchar(255)) is a good practice? since the number of
            > > transactions are also huge in a day (around 5000-6000 may be
            more
            > > also).
            > >
            > > can anybody suggest me a wayout for this scenario,
            > >
            > > Thanks in advance..
            > > Rajendra,
            > >
            > >
            > >
            > >
            > >
            > > SqlCon is a special interests group run under the aegis of MUGH -

            > http://www.mugh.net
            > >
            > > http://www.mugh.net/sql
            > >
            > >
            > >
            > > Yahoo! Groups SponsorADVERTISEMENT
            > >
            > >
            > > ---------------------------------
            > > Yahoo! Groups Links
            > >
            > > To visit your group on the web, go to:
            > > http://groups.yahoo.com/group/sqlcon/
            > >
            > > To unsubscribe from this group, send an email to:
            > > sqlcon-unsubscribe@yahoogroups.com
            > >
            > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
            > Service.
            > >
            > >
            > >
            > > ---------------------------------
            > > Do you Yahoo!?
            > > Yahoo! Small Business $15K Web Design Giveaway - Enter today
            >
            >
            >
            >
            > SqlCon is a special interests group run under the aegis of MUGH -
            http://www.mugh.net
            >
            > http://www.mugh.net/sql
            >
            >
            >
            >
            > ---------------------------------
            > Yahoo! Groups Links
            >
            > To visit your group on the web, go to:
            > http://groups.yahoo.com/group/sqlcon/
            >
            > To unsubscribe from this group, send an email to:
            > sqlcon-unsubscribe@yahoogroups.com
            >
            > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
            Service.
            >
            >
            >
            > ---------------------------------
            > Do you Yahoo!?
            > Yahoo! Small Business $15K Web Design Giveaway - Enter today
          • raghava.naraharaseti@wipro.com
            Hi, If u are planning to create index consider even the insert and update statements to the table as u r saying the transaction are around 5000-6000 a day. In
            Message 5 of 6 , Apr 7, 2004
            • 0 Attachment

              Hi,

               

              If u are planning to create index consider even the insert and update statements to the table as u r saying the transaction are around 5000-6000 a day.

               

              In batch process,   it takes more time also no problem.

              If it is Online updation or insertion then u should even consider update and insert time also.

               

              Thanks

              Raghava

               


              From: Rajendra Prasad [mailto:rajendraprasad_t@...]
              Sent: Wednesday, April 07, 2004 10:24 PM
              To: sqlcon@yahoogroups.com
              Subject: [mugh-sqlcon] Re: Query Takes long time for execution !!!!

               

              Hi Murthy,
              Thanks a lot.
              I would work on those lines and get back to u.
              Pls provide me your inputs also.

              Regards,
              Rajendra.

              --- In sqlcon@yahoogroups.com, vadlamani murthy
              <vadlamanimurth@y...> wrote:
              > Hi Rajendra,
              > This we cann't achieve at one go.  You have to make use of all the
              options that you have and then you have to apply the best which fits.

              > Please create a covered index on msgid and on subject.  The query
              should have order by clause on "Subject".  Get the execution plan
              for the query.
              > Based on the execution plan you will come to know whether the
              query is  making use of index or not and other details like I/O
              Cost,CPU Cost. 

              > Gather the results and save the same.

              > Now save the query as workload and run the index tuning wizard. 
              If it recommends any indexes go and build the same. 

              > Again get the exeuction plan details. 
              > Compare the execution plan details.

              > Go for the better one. 

              > You can also go for Full Text Search which is the other option
              that has struck my mind.  I am looking into it for various pros and
              cons.  I will give you more details in my next mail.

              > Hope this will help you.

              > Thanks
              > Murthy



              >
              >
              > Rajendra Prasad <rajendraprasad_t@y...> wrote:
              > Hi Muthry,
              >
              > Thanks for your reply.
              > The Query gets fired in a job which populates the tables from flat
              > files. I need to link up the records to their related ones, in 
              few
              > cases.
              > When that condition happens then it fires this query to find the
              > related message id with the incoming subject string.
              >
              > Thanks,
              > Rajendra.
              >
              > --- In sqlcon@yahoogroups.com, vadlamani murthy
              > <vadlamanimurth@y...> wrote:
              > > Hi Rajendra,
              > > 
              > > Before answering your question, I would like to know how often
              do
              > you fire this query.
              > > 
              > > Thanks
              > > Murthy
              > >
              > > Rajendra Prasad <rajendraprasad_t@y...> wrote:
              > > Hi all,
              > >
              > > MUGH is doing grt.
              > > I have a table with around 1 crore records in that. Has fields
              > > {msgid,Subject..}, There is a cluster index on msgid column and
              > now
              > > i want to make a search query to find all the ids with a
              speicific
              > > subject of my Interest. like:
              > >
              > > select msgid from <messagesTable>  where Subject like 'mySubject'
              > >
              > >
              > > But the query takes enormous time, Creating Index over the
              subject
              > > column (varchar(255)) is a good practice? since the number of
              > > transactions are also huge in a day (around 5000-6000 may be
              more
              > > also).
              > >
              > > can anybody suggest me a wayout for this scenario,
              > >
              > > Thanks in advance..
              > > Rajendra,
              > >
              > >
              > >
              > >
              > >
              > > SqlCon is a special interests group run under the aegis of MUGH -

              > http://www.mugh.net
              > >
              > > http://www.mugh.net/sql
              > >
              > >
              > >
              > > Yahoo! Groups SponsorADVERTISEMENT
              > >
              > >
              > > ---------------------------------
              > > Yahoo! Groups Links
              > >
              > >    To visit your group on the web, go to:
              > > http://groups.yahoo.com/group/sqlcon/
              > >  
              > >    To unsubscribe from this group, send an email to:
              > > sqlcon-unsubscribe@yahoogroups.com
              > >  
              > >    Your use of Yahoo! Groups is subject to the Yahoo! Terms of
              > Service.
              > >
              > >
              > >
              > > ---------------------------------
              > > Do you Yahoo!?
              > > Yahoo! Small Business $15K Web Design Giveaway - Enter today
              >
              >
              >
              >
              > SqlCon is a special interests group run under the aegis of MUGH -
              http://www.mugh.net
              >
              > http://www.mugh.net/sql
              >
              >
              >
              >
              > ---------------------------------
              > Yahoo! Groups Links
              >
              >    To visit your group on the web, go to:
              > http://groups.yahoo.com/group/sqlcon/
              >  
              >    To unsubscribe from this group, send an email to:
              > sqlcon-unsubscribe@yahoogroups.com
              >  
              >    Your use of Yahoo! Groups is subject to the Yahoo! Terms of
              Service.
              >
              >
              >
              > ---------------------------------
              > Do you Yahoo!?
              > Yahoo! Small Business $15K Web Design Giveaway - Enter today




              SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net

              http://www.mugh.net/sql




            Your message has been successfully submitted and would be delivered to recipients shortly.