1

I am currently refactoring a function which builds our custom WHERE clause from supplied search criteria. The one issue I have come across is user access to projects. I receive, from the claims service, a List<long> of all the project IDs a user has access to. Currently this is formatted as an IN clause:

SELECT * FROM items WHERE project_id IN (1, 2, 3, 4)

As the system scales this will balloon to possibly many thousands of IDs. I have researched how to avoid the IN clause, but mostly people use a stored procedure or create a temporary table and join to that. As of right now I have to use the claims service, and my list of IDs.

Is there a better way to do this, given my constraints? If not, I can escalate the issue to do a larger refactor. Thanks in advance.

hsimah
  • 1,265
  • 2
  • 20
  • 37
  • 1
    Are you using an ORM? – Dan Teesdale Nov 09 '15 at 22:08
  • 3
    I don't quite understand how being forced to get a list of IDs from a service prevents you from using a stored procedure. I would look into a stored procedure that receives a table valued parameter. That would let you use the service to get the values and a procedure to get the data. – Sean Lange Nov 09 '15 at 22:11
  • 1
    Did you have a look on `CREATE TYPE`? – Shnugo Nov 09 '15 at 22:11
  • 2
    How is it determiningt the project ids? If you store the project_ids a person is allowed to have through tables in the database, you can get this through simple joins or a where exists clause. – HLGEM Nov 09 '15 at 22:12
  • The system uses Entity Framework, but the search is too complex to use the usual retrieval methods (or was deemed so by the architect). I am not prevented from using a stored procedure, but doing so is outside the scope of the task I have been assigned. As stated, if that's the solution then I will do that, but I was wanting to exhaust other options before involving the DBA team. The IDs are determined by claims stored locally in our system, and also from third party systems (e.g. AD groups). The service is the sole way to get the accurate list of IDs. – hsimah Nov 09 '15 at 22:22
  • I could think of some answer that will use EF `DbSet.SqlQuery()` method, generate a query that uses a `table variable` and inserts all the IDs into that, and joins on it to produce the final result....... but it would be really hacky and bad practice. The proper way is to handle this via a stored proc and table-valued parameters. – Dmitriy Khaykin Nov 09 '15 at 22:30
  • I think I will have to use the table-valued parameter and stored procedure. I have read enough about them to do it, I have some constraints around creating stored procedures. Nothing a call to the DBA team can't resolve though. – hsimah Nov 10 '15 at 00:21

3 Answers3

1

IN Clause is a efficient method.

Best practices: never use wildcard for columns, use instead the name of the columns:

SELECT Collumn01, Collumn02, ... 
FROM items 
WHERE project_id IN (1, 2, 3, 4)

I suggest you to try adding a Index into project_id column.

This will speed up your execution.

Happy to Help you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Igor Quirino
  • 1,187
  • 13
  • 28
  • The * was only there as an example, but +1 for calling me out on it! I understand IN is quite efficient, but I am concerned about hitting the SQL Server limit. We have ~5000 projects at the moment and it will only increase. – hsimah Nov 10 '15 at 00:23
1

I am assuming your list is provided as a delimited string; I just answered a similar question on how to get around this limitation a few minutes ago on this site.

Link: Work around 'IN' clause limitation

Community
  • 1
  • 1
Mike Zalansky
  • 796
  • 7
  • 14
  • It can be formatted into a delimited string. I will give your solution a try and see how it works with the rest of my query. Thank you. – hsimah Nov 10 '15 at 00:22
0

Why do you have WHERE project_id IN (1, 2, 3, 4)? What's special about those IDs? Are there other IDs that aren't special? If there's something special about certain IDs you should create a table storing that; don't hard-code the specialness into your SQL.

CREATE TABLE Project (project_id INT PRIMARY KEY,
    is_special INT)

Then mark all your special projects:

UPDATE Project SET is_special = 1 WHERE project_id IN (1, 2, 3, 4)

From now on, check Project.is_special not individual project_ids:

SELECT whatever FROM Items i JOIN Project p ON p.project_id=i.projectID
WHERE p.is_special = 1
Dour High Arch
  • 21,513
  • 29
  • 75
  • 90
  • Sorry, I may not have been clear. Those IDs are just an example. I have a service that provides me a list of project IDs a user has access to. There are locally stored user claims, but also claims from external systems. I retrieve the list of IDs and format it into `WHERE project_id IN ({0})` etc. They aren't special. – hsimah Nov 10 '15 at 00:20
  • If you are just taking a delimited list of ids from that service and dropping them into a string Format like that, you are leaving yourself open to SQL injection. Just saying – NPearson Apr 08 '21 at 00:13