Forum Discussion
Best Practice. Using sql stored procedure vs importing data from sql server?
If running native SQL statements and/or stored procedures: performance will depend on the DB config and its load.
If importing (assuming that the dataset size does not exceed the Power BI limit), once imported, your perf depends on the xVelocity engine (aka VertiPaq) and the resources of the platform on which you're running this (your desktop or the cloud for Power BI Service). But if within the size limits, once everything is imported, you will not have the time lag that comes from going over the network and the time it takes the DB to return the result set or sets everytime you hit the DB to load additional data.
Also, keep in mind 'import' is only 1 of the 3 modes available to get your data into Power BI. The other 2 are 'composite', and 'DirectQuery'. And stored procedures cannot be called in DirectQuery mode, and there are also restrictions as regards the type and complexity of SQL statements that can be used in DirectQuery mode.
A different approach to running stored procedures will support Direct Query. Consider creating a DQ with something like this as your SQL statement:
DECLARE @return_value INT
EXEC @return_value = pbi.Delightful_Prodcedure @Offset = -7
SELECT 'Return Value' = @return_value;