Professional Web Applications Themes

How to search a large table? - Microsoft SQL / MS SQL Server

Hi guys, Mentioned question seems easy, but I can't figure out how to do this with the system I already has. Here's the deal. Today I have a table containing a lot of e-mail addresses. These belongs to a certain group. I made a Stored Procedure to get e-mail addresses and that supports paging, see below. Now I would like to be able to search the requested data. How should I do this? I need to specify what to search, like last_name or postal_code where postal_code has a certain pattern. Should I use this SP, or should I create a ...

  1. #1

    Default How to search a large table?

    Hi guys,

    Mentioned question seems easy, but I can't figure out how to do this with
    the system I already has. Here's the deal.

    Today I have a table containing a lot of e-mail addresses. These belongs to
    a certain group. I made a Stored Procedure to get e-mail addresses and that
    supports paging, see below.

    Now I would like to be able to search the requested data. How should I do
    this? I need to specify what to search, like last_name or postal_code where
    postal_code has a certain pattern. Should I use this SP, or should I create
    a new one only used for searching? Or should I maybe create the SQL
    statement at application level and send it to the SQL Server?

    Thanks for any help!

    Regards,
    Jonah Olsson

    CREATE PROCEDURE GetEmailsByGroup
    (
    group_id int,
    current_page int,
    page_size int
    )
    AS

    /* Create a temporary table to hold the current page and add an
    auto-increasing ID column */
    CREATE TABLE #TempTable
    (
    [email_id] [int] IDENTITY PRIMARY KEY,
    [email_guid] [varchar] (40) NOT NULL ,
    [email] [nvarchar] (255) NOT NULL ,
    [first_name] [nvarchar] (20) NULL ,
    [last_name] [nvarchar] (20) NULL ,
    [title] [nvarchar] (20) NULL ,
    [company] [nvarchar] (20) NULL ,
    [address] [nvarchar] (50) NULL ,
    [postal_code] [int] NULL ,
    [city] [nvarchar] (20) NULL ,
    [phone] [varchar] (20) NULL ,
    [active] [int] NOT NULL ,
    [reg_date] [datetime] NOT NULL ,
    [change_date] [datetime] NOT NULL ,
    [hard_bounces] [int] NULL ,
    [soft_bounces] [ntext] NULL
    )


    /* Fill the temp table with the page data */
    INSERT INTO #TempTable
    (
    email_guid,
    email,
    first_name,
    last_name,
    title,
    company,
    address,
    postal_code,
    city,
    phone,
    active,
    reg_date,
    change_date,
    hard_bounces,
    soft_bounces
    )

    SELECT
    EmailAddresses.email_guid,
    EmailAddresses.email,
    EmailAddresses.first_name,
    EmailAddresses.last_name,
    EmailAddresses.title,
    EmailAddresses.company,
    EmailAddresses.address,
    EmailAddresses.postal_code,
    EmailAddresses.city,
    EmailAddresses.phone,
    EmailAddresses.active,
    EmailAddresses.reg_date,
    EmailAddresses.change_date,
    EmailAddresses.hard_bounces,
    EmailAddresses.soft_bounces

    FROM
    EmailAddresses INNER JOIN
    EmailGroupMembers ON EmailAddresses.email_guid =
    EmailGroupMembers.email_guid

    WHERE
    EmailGroupMembers.group_id = group_id

    ORDER BY
    EmailAddresses.email


    /* Create variable to identify the first and last record that should be
    selected */
    DECLARE first_rec int, last_rec int
    SELECT first_rec = (current_page - 1) * page_size
    SELECT last_rec = (current_page * page_size + 1)


    /* Select one page of data based on the record numbers above */
    SELECT
    email_guid,
    email,
    first_name,
    last_name,
    title,
    company,
    address,
    postal_code,
    city,
    phone,
    active,
    reg_date,
    change_date,
    hard_bounces,
    soft_bounces

    FROM
    #TempTable

    WHERE
    email_id > first_rec AND email_id < last_rec


    Jonah Guest

  2. #2

    Default Re: How to search a large table?

    If you are looking at searching through the resultant data, then you can use
    this procedure itself. You would need to pass extra parameters to this SP
    that identifies the search fields and their values. You can then populate
    the temporary table based on the search results and provide the results to
    the user (via paging).
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Jonah Olsson" <com> wrote in message
    news:%phx.gbl... 
    to 
    that 
    where 
    create 


    SriSamp Guest

  3. #3

    Default Re: How to search a large table?

    SriSamp,

    Thanks for your reply, and sorry for all my spelling misstakes. Didn't see
    them until now =)

    Your idea sound like the best way.

    Jonah

    "SriSamp" <co.in> skrev i meddelandet
    news:phx.gbl... 
    use 


    Jonah Guest

Similar Threads

  1. Going for a LARGE Table: Any Tips?
    By Good Man in forum MySQL
    Replies: 1
    Last Post: November 11th, 05:36 PM
  2. Large SQL table causes web page to hang
    By Michael Netherton in forum ASP Database
    Replies: 2
    Last Post: July 16th, 11:49 AM
  3. search for messages in large files
    By Jman in forum PERL Miscellaneous
    Replies: 9
    Last Post: June 26th, 04:00 PM
  4. Large table creation
    By Yan-Hong Huang[MSFT] in forum ASP.NET General
    Replies: 0
    Last Post: June 25th, 07:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139