I need a way to search multiple tables while minimizing process time. The database is quite small at the moment but will grow large over time. The query will be integrated into a search by a user on a website. Would it be best to do a stored procedure or just a direct query?
Are the tables associated with eachother via a common id/column? How many table do you need to search at the same time?
The tables are associated, and I have 3. Do you think it would be okay searching 8 columns at the same time?
Stored procedures are better then query because stored procedures compile on SQL Server side only when you change but queries compile every execution. So Stored procedures are faster. There are several things you need to be careful. 1. There should be one primary key on each table 2. There should be key relation between tables, you should join 2 tables with primary key and foreign key. For a better explanation, if you write your table DDL script(I mean create table table1 bla bla script) we can help better. 3. 8 columns won't be a problem if each column content size is small and total length of a table data smaller then 8192 bytes.
With stored procedures though, would that not be a major drain on resources if the database grows considerably large?
If you create good indexes, it won't be a problem for million rows. I have worked very big tables(bank systems) and if you make a good database and query optimization, it won't be a problem.