database design - bind variable query

From: Jeff <jeffkirchhoff478_at_hotmail.com>
Date: 24 Aug 2002 13:47:18 -0700
Message-ID: <a27526e7.0208241247.5d37aab5@posting.google.com>

Hello, I'd appreciate the any comments on an approach I'm considering to enhance database information retrieval and general access efficiency.

I have a call-centre application that has literally hundreds of simple SQL statements, all almost identical, that query an Oracle (v8.17) database on NT & Win2K servers from about 70 clients. The database servers thrash during working hours & the users suffer with significant dataretrieval delays.

All the SQL queries are simple, typified by the three examples below, and the queries generated ad-hoc by the client code.

1. SELECT COUNT(*) FROM tblData WHERE field1Type='A' AND field2Value=20;
2. SELECT COUNT(*) FROM tblData WHERE field1Type='B' AND field2Value=16 OR field5Type='J' AND field5Value=18;
3. SELECT COUNT(*) WHERE field1Type='B' AND field2Value=16 OR field1Type='G' AND field1Value=18 OR field7Type='J' AND field7Value=23 OR field9Type='X' AND field9Value=7;

So, as you have no doubt guess I've decided to use bind variables and expect to hopefully improve database caching on the server with knock-on speed improvements client side.

I originally thought I should create many different PL/SQL stored functions, each with a different number of bind variables, for each of the queries with one set of values (query 1 above), for two sets of values (query 2 above), for ...., for 4 sets of values (query 3 above) etc. But where I need advice is on whether there a better way of approaching the problem because I'm doubting that dozens (the real app has more than 4 pairs as shown in 3. above) of stored functions with bind variables would be much better. I think there might be and my current line of thinking is that I should create **one** stored function with 4 parameters pairs and when I need to perform a query with less than 4 parameters I just duplicate the input variable values.

For example, assuming the table created using [CREATE TABLE tblTest(datatype1 VARCHAR2(1), datavalue1 NUMBER)]

--Something like this would be my stored function

CREATE OR REPLACE FUNCTION testFunction(type1 VARCHAR2, value1 NUMBER, type2 VARCHAR2, value2 NUMBER, type3 VARCHAR2, value3 NUMBER, type4 VARCHAR2, value4 NUMBER) RETURN NUMBER AS xcount NUMBER;
BEGIN
 SELECT COUNT(*)
  INTO xcount
  FROM tblData

  WHERE (datatype=type1 AND datavalue=value1)
     OR (datatype=type2 AND datavalue=value2)
     OR (datatype=type3 AND datavalue=value3)
     OR (datatype=type4 AND datavalue=value4);

 RETURN xcount;
END;
--And something like this would be the 3 reformatted queries initially
shown above

1. SELECT testFunction('A',20,'A',20,'A',20,'A',20) FROM DUAL;
2. SELECT testFunction('B',16,'B',16,'J',18,'J',18) FROM DUAL;
3. SELECT testFunction('B',16,'G',18,'J',23,'X',7) FROM DUAL;

So is there a better way still or do you think I'm on the right track with this approach and it will increase searching efficiency & increase caching on the server.

A few relevant facts not in the mesage body above

1. things are a lot more complicated in terms of the SQL query. The query has 3 inner joins across 3 tables, I've just kept things simple for the question at hand above.
2. The number of pairs of variables that would go into the function would be about 40 (and not 4 as shown above).
3. These tables are large & our hardware pretty good spec. Most of the relevant table columns are already indexed - there doesn't seem to be another way of increasing speed of access that we can do without changing our access model.
4. Although I'd like to change the organisation of the underlying data, unfortunately I don't have the luxury as there are many other applications that rely on the existing structure and I don't want to go here.

Many thanks & I hope to receive a comment or two. Best regards
Jeff Received on Sat Aug 24 2002 - 15:47:18 CDT