SQL behind the PDM "Search.exe" tool

Discuss SolidWorks PDM
User avatar
jcapriotti
Posts: 1897
Joined: Wed Mar 10, 2021 6:39 pm
Answers: 32
Location: The south
x 1236
x 2029

SQL behind the PDM "Search.exe" tool

Unread post by jcapriotti »

Does anyone know the command line switch (argument) to output the T-SQL code behind the Search.exe for a search card? I had it years ago and can't find it.
Jason
User avatar
AlexB
Posts: 508
Joined: Thu Mar 18, 2021 1:38 pm
Answers: 29
x 274
x 462

Re: SQL behind the PDM "Search.exe" tool

Unread post by AlexB »

This is all I can find at the moment.
image.png
image.png (10.83 KiB) Viewed 2673 times
User avatar
jcapriotti
Posts: 1897
Joined: Wed Mar 10, 2021 6:39 pm
Answers: 32
Location: The south
x 1236
x 2029

Re: SQL behind the PDM "Search.exe" tool

Unread post by jcapriotti »

AlexB wrote: Wed Dec 07, 2022 3:20 pm This is all I can find at the moment.
image.png
Thanks.....those are documented, we use a few. This one is hidden and I can't find it. grumph
Jason
User avatar
bnemec
Posts: 1954
Joined: Tue Mar 09, 2021 9:22 am
Answers: 10
Location: Wisconsin USA
x 2562
x 1411

Re: SQL behind the PDM "Search.exe" tool

Unread post by bnemec »

I'm curious, what'cha doing?

Is this the tsql we use in the report generator tool?

@Diaval might know this command line switch or the KB solution that has it. I tried looking it up there but no luck. Although I did learn how to edit my shortcut to search.exe to start with my "default" search card to save the three clicks getting to it in the tree.
User avatar
jcapriotti
Posts: 1897
Joined: Wed Mar 10, 2021 6:39 pm
Answers: 32
Location: The south
x 1236
x 2029

Re: SQL behind the PDM "Search.exe" tool

Unread post by jcapriotti »

bnemec wrote: Wed Dec 07, 2022 5:32 pm I'm curious, what'cha doing?

Is this the tsql we use in the report generator tool?
Searches for files just by "State" using the default "Complete Search" card take 25-30 seconds (This seems way too long). Whereas searching on a variable takes 2-3 seconds. Doesn't matter if its 1 file found or 100.

We have 830,000 entries in the Documents table. Tried this in our current version (2020sp5) and also in 2022sp5.....no difference.

Troubleshooting long ago, like over 10 years, I had the command line switch...or maybe it was a registry entry. What is did was save a text file after every search that contained the T-SQL that the search card was using behind the scenes. This was useful to figure out what the search was actually doing.
Jason
User avatar
JSculley
Posts: 648
Joined: Tue May 04, 2021 7:28 am
Answers: 55
x 9
x 888

Re: SQL behind the PDM "Search.exe" tool

Unread post by JSculley »

SQL Server Management Studio provides query tracing and can show you the execution plan for the query. Make sure you enable tracing of RPC Starting events to capture the correct SQL query.

Pasting these queries into Management Studio, a search by state (on my server) is about the same speed as a search for a particular variable value ( 30-40 secs) with each result sets of 48000 and 53000 rows respectively with 226,700 rows in the Documents table.
User avatar
Diaval
Posts: 87
Joined: Wed Mar 17, 2021 12:01 pm
Answers: 7
Location: Stockholm
x 50
x 110

Re: SQL behind the PDM "Search.exe" tool

Unread post by Diaval »

I think there is a registry setting that can be used to spit out the query used from the search tool but I don't have details about it. You could ask your VAR and see if they are able to get it from SW support.
-- To espouse elucidation we must eschew obfuscation
User avatar
jcapriotti
Posts: 1897
Joined: Wed Mar 10, 2021 6:39 pm
Answers: 32
Location: The south
x 1236
x 2029

Re: SQL behind the PDM "Search.exe" tool

Unread post by jcapriotti »

Diaval wrote: Wed Jan 11, 2023 4:27 am I think there is a registry setting that can be used to spit out the query used from the search tool but I don't have details about it. You could ask your VAR and see if they are able to get it from SW support.
VAR didn't know. However, Jeremiah Davis did respond on my Linkedin post that it is a registry key.
Under HKLM, down to PDMWorksEnterprise, create a DWORD DebugSQL with a value of 1. This creates a Search.txt at the root of the C drive. But with current security settings that I typically see, you have to run Explorer as administrator (search for Explorer, right-click, run as administrator) in order for this to create the text file.
I had to go directly to the "SEARCH.EXE" in the PDM and "run as admin" to get it to work. One of my add-ins throws an error when loading the search card but it does create the text file of the SQL query when run. I believe this setting goes back to WinXP so all of the extra security on Win10 must be causing the add-in errors.
Jason
User avatar
AlexB
Posts: 508
Joined: Thu Mar 18, 2021 1:38 pm
Answers: 29
x 274
x 462

Re: SQL behind the PDM "Search.exe" tool

Unread post by AlexB »

Wow, it's pretty awesome that you were able to track this down. This will definitely be going in my "obscure things that might help" file :)
Post Reply