0

I'm having a poor performance in joins with user defined tables (when more than one rows exists) Reading other posts like Performance of User-Defined Table Types in SQL Server i've tried to fix it but i've not obtained good results. I'll explain my case: I have this table type defined:

CREATE TYPE myType AS TABLE 
(
            [Id] [int] NOT NULL,
            [Value] [varchar](500)
)

and i use it in the following sp:

DECLARE @TableParams myType 

INSERT INTO @TableParams (Id, Value)
--Code to fill the table type variable
SELECT ........ from......
--table type variable filled 

SELECT @NumberParams =count(1) from @TableParams 
SELECT T0.col1 from T0 where  (@NumberParams =(
                select count(1) from T1
                    INNER JOIN @TableParams T2
                    on T1.Id = T2.Id
                    and T1.Value=T2.Value
                    where T1.col1= T0.col1

                ))OPTION(RECOMPILE)

as i've said I've tried all the options (except the option recompile in subselect) mentioned on attached post, but no good news. The option recompile option in the subselect i have not tested it because when i tried to compile the sp, it always returned an error

Community
  • 1
  • 1
user4919313
  • 125
  • 11
  • 1
    Why are you using table variables and what are you trying to do with them? Why not use a real table with indexes? – Panagiotis Kanavos Jan 16 '17 at 09:05
  • Shouldn't that be `INSERT INTO @TableParams` and `SELECT ... FROM @TableParams` ? – Peter B Jan 16 '17 at 09:06
  • 1
    What are you trying to achieve with this query? Using counts in WHERE is *very* unusual. Perhaps you could rewrite the query in a much more efficient way? What is it supposed to do? – Panagiotis Kanavos Jan 16 '17 at 09:07
  • Because i receive as a input parameter a string like this (id1=value1,id2, value2.......) and to make easier the search (i can use it with inner join) i put this string in a table parameter – user4919313 Jan 16 '17 at 09:09
  • Changed the table type naming that it was not properly defined in the sample – user4919313 Jan 16 '17 at 09:11
  • You could add a primary key to your table type, that might help performance. However, it's hard to know where the bottle neck is without looking at the entire query and it's execution plan. – Zohar Peled Jan 16 '17 at 09:18
  • 2
    If you explain the logic I think query can be rewritten. Am sure the query itself is inefficient – Pரதீப் Jan 16 '17 at 09:24

0 Answers0