December 6, 2015 at 10:56 pm
Hi,
I am have nearly 10000 records in a table and i'm using these data's for creating analytics in Tableau.
I want to know If i directly pull data from the table or creating a view or user defined function will give me best performance.
I will get more data's on the daily basis and it can go upto 2 lac records and tableau applies some aggregate calculations over these data's.
So can anyone let me know which will give me the best performance?
December 6, 2015 at 11:27 pm
a view would probably be simplest. But test it for yourself. Clear the buffers on your dev server (not the production server!!!), set statistics IO on, set statistics time on, run your query. Compare. Oh, and compare query plans.
Depends on the UDF, but usually views are faster than UDFs. (Track down Kevin Boles' article "Death by UDF").
December 7, 2015 at 12:26 am
The Death By UDF article from Kevin is focused primarily on the misuse and overuse of Scalar-valued Functions (SVFs) and Multi-statement Table-valued Functions (mTVFs). Inline Table-valued Functions (iTVFs) do not suffer from the same problem SVFs and mTVFs do making them a safe choice for encapsulating code in many more circumstances.
Direct table access, a VIEW containing a query that does direct table access and an Inline Table-valued Function (iTVF) that does direct table access will all perform, for practical discussion purposes, equally so choose the technique that best suits your requirements. Usually keeping reports away from querying tables directly gives you some flexibility from a security standpoint and for refactoring database tables down the line. You can think of iTVFs as a parameterized view which can be handy in some instances depending on the query form you need.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 7, 2015 at 4:03 am
I'm with Orlando. At the root, all three approaches are fundamentally the same and will all perform equally well. It depends on your implementation and requirements as to which is the best in a given situation. If you posted more details on what it was that you were attempting, it would be easier to understand if one of the methods would work better in your situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2015 at 10:23 am
You'll get best performance only from properly clustering the underlying table. Then either a direct query or a view should perform just fine.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 10:54 am
I'm with what everyone has said so far. I would add, however, that, if you are doing aggregations, you might want to consider an indexed view with the aggregations indexed. This will be quicker than performing the aggregations at run-time.
-- Itzik Ben-Gan 2001
December 7, 2015 at 11:28 am
Alan.B (12/7/2015)
I'm with what everyone has said so far. I would add, however, that, if you are doing aggregations, you might want to consider an indexed view with the aggregations indexed. This will be quicker than performing the aggregations at run-time.
Or, if doing aggregations, look to columnstore indexes as a possible aid.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2015 at 3:15 pm
Alan.B (12/7/2015)
I'm with what everyone has said so far. I would add, however, that, if you are doing aggregations, you might want to consider an indexed view with the aggregations indexed. This will be quicker than performing the aggregations at run-time.
Be careful with indexed views.
They are effectively tables (or - materialized views) which get updated by every single transaction performed on any of the base tables involved in the view.
If the application submits data by doing so called "iterational updates" (unfortunately, it's a quite popular technic) then indexed view(s) on the tables involved in such update may spell the death of the system.
_____________
Code for TallyGenerator
December 7, 2015 at 4:33 pm
Sergiy (12/7/2015)
Alan.B (12/7/2015)
I'm with what everyone has said so far. I would add, however, that, if you are doing aggregations, you might want to consider an indexed view with the aggregations indexed. This will be quicker than performing the aggregations at run-time.Be careful with indexed views.
They are effectively tables (or - materialized views) which get updated by every single transaction performed on any of the base tables involved in the view.
If the application submits data by doing so called "iterational updates" (unfortunately, it's a quite popular technic) then indexed view(s) on the tables involved in such update may spell the death of the system.
ThatTrue that. I started to make mention of the costs/gotchas associated with indexed views but decided to keep it brief.
From the sounds of it (and until the OP chimes back in I'm making some assumptions), the OP is not dealing with a large number of records and would be doing a daily load of the data to some kind of reporting table. That is, IMHO, a great place for an Indexed View (or Columnstore index as Grant mentioned) since the table is only going to be written to once daily and we know that aggregations will be performed. In this case, too, my recommendation is to drop the index before the daily load then re-create the index and update the stats afterwards.
Edit: corrected a typo.
-- Itzik Ben-Gan 2001
December 10, 2015 at 7:13 am
Hi,
I am thinking of creating a column store index for the table i am using?
Is there any specifications for creating it?
December 10, 2015 at 7:54 am
karthik82.vk (12/10/2015)
Hi,I am thinking of creating a column store index for the table i am using?
Is there any specifications for creating it?
Yes those specifications can very easily be found by looking at BOL (books online), the documentation that is used to support sql server. If you are not sure you might try using a search engine like google or bing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2015 at 8:07 am
karthik82.vk (12/10/2015)
Hi,I am thinking of creating a column store index for the table i am using?
Is there any specifications for creating it?
Yeah, tons! Go and read the documentation. It's vital.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2015 at 6:42 am
Getting back to the original basis of the question of Tableau and SQL Server. I use Tableau with SQL on a daily basis, there are times that the sql that is generated from Tableau just does not perform well. You can have a query that runs fine on SQL Server itself but when you try to use it with Tableau and add filters, calculations, etc., the sql generated is not optimum. You can use performance recording in Tableau to see where the bottlenecks are and hopefully try to find the offending bad sql. Problem is you may not be able to do anything about it except use an "Extract" for the Tableau workbook. You can then use a "differential" extract process to load new rows everyday.
December 11, 2015 at 6:56 am
rustman (12/11/2015)
Getting back to the original basis of the question of Tableau and SQL Server. I use Tableau with SQL on a daily basis, there are times that the sql that is generated from Tableau just does not perform well. You can have a query that runs fine on SQL Server itself but when you try to use it with Tableau and add filters, calculations, etc., the sql generated is not optimum. You can use performance recording in Tableau to see where the bottlenecks are and hopefully try to find the offending bad sql. Problem is you may not be able to do anything about it except use an "Extract" for the Tableau workbook. You can then use a "differential" extract process to load new rows everyday.
I've never worked directly with Tableau, so please excuse the ignorance of the question...
Assuming you're directly accessing tables & views through Tableau, you are going to have little control over the shape of the query that results as you add in additional behaviors, filters, etc. Can you call stored procedures from Tableau? That might be a way around some issues, move the processing closer to the database (as much as that bothers some in the development community these days).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply