Professional Web Applications Themes

SQL Stored Proc question - Coldfusion Database Access

I have a rather large cfquery slowing me down. i can build simple stored procs but I dont know anything advanced. Can you take a cfquery like this <cfquery name="getname" datasource="dsn"> SELECT name, address, zipcode, city, state,etc,etc,etc FROM users WHERE 0=0 <cfif form.name IS NOT "">AND name = '#form.name#'</cfif> <cfif form.zipcode IS NOT "">IN zipcode = '#form.zipcode#'</cfif> <cfif form.cityIS NOT "">IN city = '#form.city#'</cfif> </cfquery> I have like 15 if's in trhe statement. Is this feasible in SQL stored procs or will it actually hurt performance more?...

  1. #1

    Default SQL Stored Proc question

    I have a rather large cfquery slowing me down. i can build simple stored procs
    but I dont know anything advanced.

    Can you take a cfquery like this

    <cfquery name="getname" datasource="dsn">
    SELECT name, address, zipcode, city, state,etc,etc,etc
    FROM users
    WHERE 0=0
    <cfif form.name IS NOT "">AND name = '#form.name#'</cfif>
    <cfif form.zipcode IS NOT "">IN zipcode =
    '#form.zipcode#'</cfif>
    <cfif form.cityIS NOT "">IN city = '#form.city#'</cfif>
    </cfquery>

    I have like 15 if's in trhe statement. Is this feasible in SQL stored procs or
    will it actually hurt performance more?

    Swd1974 Guest

  2. #2

    Default Re: SQL Stored Proc question

    Hi SWD1974,

    Moving data-centric actions into the data bubble makes good sense. If you
    migrate your SQL statement to an SP, you'll realize some performance gains,
    clean up your code, and put the heavy lifting where it belongs.

    Given your example, (and using MS SQL), you could create an SP:

    CREATE PROCEDURE gensp_GetUsers
    p_vusername varchar(64),
    p_vzipcode varchar(12),
    p_vcity varchar(64)

    AS
    declare l_vSQL varchar(2048) -- adjust for size up to 8000 if necessary
    set l_vSQL = 'SELECT name, address, zipcode, city, state,etc,etc,etc
    FROM users
    WHERE 0=0 '
    IF p_vusername <> ''
    set l_vSQL = l_vSQL + 'AND [name] IN ('''+l_vusername+''')'
    IF p_vzipcode <> ''
    set l_vSQL = l_vSQL + 'AND zipcode IN ('''+l_vzipcode+''')'
    IF p_vcity <> ''
    set l_vSQL = l_vSQL + 'AND city IN ('''+l_vzipcode+''')'
    --etcetcetc
    exec(l_vSQL)

    and call it with CFSTOREDPROC.

    SQL caches the plan for the SP execution, so after the first run, the
    performance is markedly superior to issuing an ad hoc query.

    HTH,


    philh Guest

  3. #3

    Default Re: SQL Stored Proc question

    OK, crow eaten here. Constructing dynamic SQL does nothing for performance
    gain (as has been pointed out so pointedly by Joe Celso et al.) But you should
    still come up with a parameterized SP and let the DB do the lifting.

    Mea culpa, mea culpa,....

    philh Guest

Similar Threads

  1. Simple Stored Proc Question
    By bzydaddy in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 2nd, 01:45 PM
  2. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. Simple stored proc in query question
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:20 PM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 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