Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dipeshrm
Starting Member
3 Posts |
 Posted - 2005-10-19 : 15:29:34
|
I have a really weird situation and any help here will really be appreciated.Let me explain my problemI am busiding a web application where this situation arose. I am using ASP.Net using VB.Net as my application server language of development. Lets say I have two tables TableA and TableB with their (highly simplified) structures as shown below.PrimID is the autoincrement primary key for TableA and ForeignID from TableB is the corresponding foreign key.TableA=====================================PrimID Criteria1 Name State=====================================1 A Greg MD2 A Lisa NY3 B Greg NY4 B Keith VA5 B Tim MA6 C Sara WY=====================================TableB=======================ForeignID Criteria2=======================2 XYZ3 PQR3 CDV4 CDV4 OTS5 PNG2 GJZ=======================There can be duplicate entries for Names in TableA, but Names are unique for a given Criteria1. Similarly there can be duplicate entries for Criteria2 in TableB, but Criteria2 is unique for a given ForeignID.I want a way to provide the end user a way that they can dynamically query these two table with a great amount of complexity. In plain English they should be able to do something like:Select all unique Names from TableA where (Criteria1=A and Criteria1=B and Criteria2=PQR AND Criteria2=CDV) or ((Criteria1=C or Criteria1=D) and Criteria2=GJZ and Criteria2=PNT)This above is a very simpler version of what I want to do, but any insight to this scenario will help me extend the concept to a more complicated scenario.I am done with the interface that collects the criteria,grouping and the logical operators and stores them in a database, but how to make use of that collected data and make an efficient query out of it is my problem  If I used temp table I can end up with over 10 temp tables with a relatively simple query and if I use joins (including self joins) I can end up with over a 10 way join with a relatively simple query.Again, any help will be much appreciated-Dipesh |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-19 : 15:46:06
|
| Hi dipeshrm, Welcome to SQL Team!Can you not just create some Dynamic SQL and fire that at the database?Kristen |
 |
|
|
dipeshrm
Starting Member
3 Posts |
Posted - 2005-10-19 : 16:19:05
|
| Hi Kristen,I am fairly new to SQL Server, I have been working on MySQL for the past 2 years and we just switched to SQL Server. I have never used Dynamic SQL before. Seems like its time now to start learning it.Do you think it would run a dynamic query relatively efficiently given that each of the tables in my example have 2-5 million records in them?If you could also point me to a good resource/example that should be great!Thanks a bunch! -Dipesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-19 : 19:51:07
|
By dynamic SQL I just meant to have your Client Application (or a Stored Procedure if you prefer that) build a Text String containing the SQL query and then execute that - it could be different each time, and only needs to contain the parts of the query that the user chooses.Once you've got that working you can then parameterised it and use sp_executeSQLto actually execute the SQL command string. The idea is that you have queries which have identical text, but the parameters will be substituted with different values each time - and the query plan will be cached. So ... as long as the query SQL strings get reused to some extent you will take advantage of the query plan caching.Then you can mess around with indexes to improve performance if necessary  Have a look at sp_executeSQL in the Help File ("Books online")Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
dipeshrm
Starting Member
3 Posts |
Posted - 2005-10-20 : 08:10:00
|
| Hi Kristen and Madhivanan,First of all thanks for responding to my questions.I figured how stupid I was right after I replied back to your post about "dynamic" sql ... For a sec there I thought you meant for me to implement some SQL server programming.I was just not familiar with the term "dynamic" sql coz mySQL did not support stored procedure and hence everything was just (dynamic) SQL.In fact in a way my question was what sql statement would i write for e.g. to pull out unique names from table A where criteria1 is A and B and C and B and E and F ... etc In other words pull out all the unique names that are common to criteria A as well as B as well as C ..etc.Please check out the web interface for collecting the requested parameters for forming the query. You will notice that there is grouping used with unlimited levels (group inside group inside group...as deep as you can go...is permitted)The dashed borders on those tables define a group.[http://69.56.194.122/creatives/Dipesh/example.gif]Please also let me know if you want the exact structures of the 4 tables involved (my 2 table example was highly simplified)Thanks!Dipesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-20 : 14:48:48
|
| "mySQL did not support stored procedure and hence everything was just (dynamic)"Far be it for me to suggest that folk on SQL Team would like arrivals for MySQL to feel as small as possible Kristen |
|
|
|
|
|
|
|
|
Executing a dynamically generated query