I have a java web application that is a front end to a Microsoft SQL Server (I think 2008)
database. It lets the user add, change, and delete records in the database. The app does not know anything about the database and gets the names of tables and fields in the tables by querying the database with queries like...
SELECT C.ORDINAL_POSITION, C.COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, K.ORDINAL_POSITION AS PK_ORDINAL, CONSTR.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.COLUMNS
C LEFT JOIN INFORMATION_SCHEMA.KEY_COL
UMN_USAGE K ON C.TABLE_NAME=K.TABLE_NAME AND C.COLUMN_NAME=K.COLUMN_NAM
E LEFT JOIN INFORMATION_SCHEMA.TABLE_C
CONSTR ON K.CONSTRAINT_NAME=CONSTR.C
NAME WHERE C.TABLE_SCHEMA='dbo' AND C.TABLE_NAME='tbl_L_Consta
nts' ORDER BY C.ORDINAL_POSITION
ORDINAL_POSITION COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH NUMERIC_PRECISION PK_ORDINAL CONSTRAINT_TYPE
1 Source_System varchar 10 NULL 1 PRIMARY KEY
2 Product_Id int NULL 10 2 PRIMARY KEY
3 Field_Name varchar 100 NULL 3 PRIMARY KEY
4 Field_Value varchar 100 NULL NULL NULL
5 Field_Purpose varchar 100 NULL NULL NULL
On the add and change screen I display to the user a drop-down list for every field, showing the unique values in that field. But first I do a count on the unique values so I know if there are too many values to display, and I cut it off at some number.
The problem is that first I have to query the database for the list of fields in the table, then I have to run a query for each field to find out the count, then if the count is not too high, I run another query to get the actual values.
I wonder if there is any way to reduce the number of queries? Maybe I could run a query that joins the actual table and syscolumns and maybe uses GROUP BY somehow to get the count of unique values in each field in the table, all in one results set.
Then, I could walk that and run a query for each field that doesn't have too many values.
Here are the other two queries I run
SELECT COUNT(DISTINCT Call_Type) AS NumFound FROM Bonds
SELECT DISTINCT Call_Type, COUNT(*) AS NumFound FROM tbl_M_Bond GROUP BY Call_Type ORDER BY Call_Type
That second query reminds me that in the drop-down list I display the value and the number of occurrences of the value. So, something like this for the first name field in a table of people's names:
Can anyone think of a way to reduce these queries and enhance performance? Without writing a stored procedure? In my organization that is more difficult to get into production.