Create different format for a table

Question

I have a table with over 500 columns, dynamically created and named by the user. New columns can be created by the user, but none can be deleted.

I was given the task to program a keyword search that searches through all columns for a specific string, and returns the ID of that record. As you can imagine, the query currently looks something like:

SELECT form_id FROM table_name WHERE col1 LIKE '%str%' OR col2 LIKE '%str%' or col3 LIKE '%str%'.. etc.

It is unbelievably slow. To combat this, I'm trying to create another table, where this data is stored in a different format like this:

form_id, col_name, value

1, 'col2', 'some random value'
1, 'col1', 'another random value'

And then searching using:

SELECT id FROM new_table_name WHERE value LIKE '%str%'

I can export all the data and format it, and insert it into the new table. But how would I go about keeping the new table updated? Is it possible to have triggers that automatically insert/update the new table when the original one is modified? Even though I don't know the column names before hand?

@DaleK Would that work even though there are many columns that I want to search through? I assumed an index of any kind wouldn't be a good solution since there's hundreds of columns, and more are added over time by the user. — Essam Al-Mansouri, Feb 11 '20 at 16:24

John Cappelletti · Accepted Answer · 2020-02-11T00:16:39.340

2

Another option just for fun

Declare @YourTable Table (EmpID int,EmpName varchar(50),Salary int,Location varchar(100))
Insert Into @YourTable Values 
 (1,'Arul',100,null)
,(2,'Jane',120,'New York')

If 2016+ use JSON

Select*
 From  @YourTable A
 Where (Select A.* For JSON Path,Without_Array_Wrapper ) like '%Jane%'

If <2016 use XML

Select*
 From  @YourTable A
 Where (Select A.* For XML Raw ) like '%Jane%'

Both would Return

EmpID   EmpName Salary  Location
2       Jane    120     New York

If you want an exact match you can quote the string as such '%"Jane"%'

edited Feb 11 '20 at 00:16

answered Feb 11 '20 at 00:11

John Cappelletti

79,615
7
44
66

The two select statements actually worked pretty well and didn't take too long to execute. So far, it's the ideal solution since it doesn't require creating any additional tables or anything. However, I also need to find a method that is compatible with Oracle. Is there a way to do this in Oracle as well? – Essam Al-Mansouri Feb 11 '20 at 00:37
@EssamAl-Mansouri Not an Oracle guy. If it helps, the XML and JSON are just formatted strings of the record. – John Cappelletti Feb 11 '20 at 12:51
Is the JSON version faster than XML? Why would I use it instead of the XML version if the XML version is compatible with old SQL servers as well? – Essam Al-Mansouri Feb 11 '20 at 16:22
@EssamAl-Mansouri The JSON is a nudge faster, but that will apply to 2016+ while the XML will support 2005+ – John Cappelletti Feb 11 '20 at 16:26
Is it possible to not include the column names in the search? For example, if one of the columns is named Jane, the rows with any value in that column ends up showing up in the results. I only want to search the values. How would I go about that? – Essam Al-Mansouri Feb 11 '20 at 19:44
@EssamAl-Mansouri It is possible (I think), but the performance would dramatically suffer. Both methods would have to parse the JSON or XML and some sort of string_agg() The following function will create a string from the record (delimited or not) https://stackoverflow.com/questions/60130261/transform-a-select-query-to-string/60130399#60130399 – John Cappelletti Feb 11 '20 at 20:03

score 1 · Answer 2 · answered Feb 10 '20 at 23:51

You can construct the table by unpivoting the original table:

select t.form_id, v.col, v.value
from t cross apply
     (values ('col1', col1), ('col2', col2), . . . ) v(col, value);

You can then keep it up-to-date with insert and delete triggers for existing data. Then you will need DDL triggers to handle users adding new columns.

John Cappelletti · Answer 3 · 2020-02-11T00:03:17.477

Seems like you are looking for an EAV model.

Here is one approach that does NOT require you to list the 500 columns.

Full Disclosure: This is NOT recommended for HUGE tables. UNPIVOT is more performant.

Also note that if you DON'T want null values remove ,ELEMENTS XSINIL

Example

Declare @YourTable Table (EmpID int,EmpName varchar(50),Salary int,Location varchar(100))
Insert Into @YourTable Values 
 (1,'Arul',100,null)
,(2,'Jane',120,'New York')

Select Entity = A.EmpID
      ,C.*
 From @YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW,ELEMENTS XSINIL) as xml))) B(XMLData)
 Cross Apply (
                Select Attribute  = a.value('local-name(.)','varchar(100)')
                      ,Value      = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./*') as C2(a)
             ) C

Returns

Entity  Attribute   Value
1       EmpID       1
1       EmpName     Arul
1       Salary      100
1       Location         <<-- NULL values display as an empty string ... see note regarding nulls
2       EmpID       2
2       EmpName     Jane
2       Salary      120
2       Location    New York

EDIT - If 2016+ ... JSON

Select A.[EmpID]
      ,Attribute = B.[Key]
      ,Value     = B.[Value]
 From  @YourTable A
 Cross Apply ( Select * From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) ) B

Create different format for a table

3 Answers3