View with calculating field is slow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

View with calculating field is slow

Author

Topic

ArnoldG
Starting Member

36 Posts

Posted - 2015-03-04 : 09:11:58

Hi,
I am querying a view on a table that I am not allowed to alter.
In that view I have used this concatenation to make a unique combination of Item guid and supplier code:


SELECT
    Col A, Col B, --etc
    CONVERT(nvarchar(36), sysguid) +'|'+crdnr	AS [SupplCode] 
FROM table A

sysguid (uniqueidentifier)
crdnr (char(6),null)

Now I have to match 500.000+ Items in Excel against that calculating [SupplCode] which is quite slow since it is calculating and converting into a string value.

I could also concatenate the table ID + crdnr, but since I need to have a unique value I cannot simply add this up.

Can I speed this up somehow without altering the table, or is there another way to identify a unique row from these joined tables?

Thanks for helping me out here.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-04 : 12:05:52

Try

cast(row_number() over (partition by crdnr order by crdnr) as varchar(32)) + '|' + crdnr as [SupplCode]

That may or may not help for two reasons a) this also involves a cast/convert, and b) your conjecture that it is converting the guid that is causing the performance problem may not be correct.

Also, it would be preferable to change the order by clause in the row_number() function to something that is deterministic based on some other column.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-04 : 16:35:58

Is the view being read into Excel?

Or is the Excel data being loaded into SQL?

How specifically is the match/join being done?

ArnoldG
Starting Member

36 Posts

Posted - 2015-03-05 : 04:26:47

James, thanks for your answer,
Unfortunately this does not seem to work okay, and I do not understand your solution enough to get it to work.

Scott, thanks for your help too.
In Excel I am searching and filtering through an SQL table of 500.000+ rows and retrieving one specific item at a time.
I am retrieving [SupplCode] which is a combination of a unique identifier of the item [sysguid] and a unique supplier code [crdnr].
Once found the item I am updating all item information via the view I described, with the [SupplCode] string in the WHERE clause to match the right record.

If I only use the [sysguid], the query retrieves all item and supplier information real fast.

If I use the unique item/supplier combination [SupplCode] it takes about 8 times longer to get the same information.

My guess is it has to do with the calculated field concatenation.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-06 : 10:28:28

Again how specifically how are you doing the lookup and UPDATE? What is the actual code for the lookup and the UPDATE?

ArnoldG
Starting Member

36 Posts

Posted - 2015-03-07 : 08:32:34

The view is quite long, but it comes down to this:


ALTER VIEW [dbo].[_FO_QP_Items_4_0] AS
SELECT
    Col A ,Col B  --etc
    ,CONVERT(nvarchar(36), sysguid) +'|'+crdnr	AS [SupplCode] 
FROM table A

The query on the view is:


SELECT *
FROM [dbo].[_FO_QP_Items_4_0]
WHERE [SupplCode] = 'DF007704-38EF-4389-8544-E70CC32404D5| 60102'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources