Desactivate sp_prepexec

6 posts, 0 answers
  1. Olivier
    Olivier avatar
    146 posts
    Member since:
    Nov 2010

    Posted 02 Mar 2015 Link to this post

    Hello

    I would like to desactivate this procedure on telerik data access.

    Cause i start a query with a little field in my select, i want to get a error, to write all field i need.

    And telerik start sp_prepexec to replace a low query and i don't get an error.

    Please help , i want to optimize my code, and i don't wand automatism about this.

    Olivier
  2. Ady
    Admin
    Ady avatar
    588 posts

    Posted 05 Mar 2015 Link to this post

    Hello Olivier,

     What do you exactly mean by 'I would like to desactivate this procedure on telerik data access.'? 
    DataAccess does not use this procedure in the runtime.

    Can you elaborate further on what your exact problem is?

    Regards,
    Ady
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Olivier
    Olivier avatar
    146 posts
    Member since:
    Nov 2010

    Posted 08 Mar 2015 in reply to Ady Link to this post

    Hello

    May be behind telerik dataaccess you use Entity Framework, i don't know who is start sp_prepexec

    I use my own method to get a Ilist in telerik dataaccess, in my partial class EntitesModel like this

    public IList<T> GetFillDataSql<T>(string cSql) {
             ReadOnlyCollection<T> oCollection = null;
                 if ( ! string.IsNullOrEmpty(cSql)) {
                     using ( IDbConnection connection = this.Connection )
                     {
                         // 2. Create a new command.
                         using ( OACommand command = this.Connection.CreateCommand() )
                         {
                             // 3. Set the command text.
                             command.CommandText = cSql;
                             using (OADataReader executeReader = command.ExecuteReader())
                             {
                                 IEnumerable<T> result = this.Translate<T>(executeReader);
                                 IList<T> list = new List<T>(result);
                                 oCollection = new ReadOnlyCollection<T>(list);
                             }
                             command.Parameters.Clear();
                             command.Dispose();
                         }
                     }
                   } // fin du If Not Empty cSql
             return oCollection;
         } // Fin du GetFillData

    If in my Query i have not all the field  from my table , i see in the profiler the sp_prepexec start after my query to get all the rest of the field. i don't know why, and it 's heavy for my sql server.

    For example, i want just Two Field, in my profiler i see my query , plus the sp_prepexec want to load other field in my table

    Have you idea please ? Do you understand why it's work like ?

    thanks

  5. Ady
    Admin
    Ady avatar
    588 posts

    Posted 09 Mar 2015 Link to this post

    Hello Olivier,

     When you execute a SQL query and map the result of the query to a Persistent type via the Translate method, the returned instances are managed by the DataAccess context.
    If your query returns only a few fields of the type and you later on access a field which is not part of the query, DataAccess will execute the required SQL to obtain the value of this field from the server. This is the reason why you see additional SQL being executed.
    If you want to fetch instance of a persistent type why do you need to use SQL? Is it not possible to use a LINQ statement instead. Note that even in this case the moment you access a field that has not been fetched from the server, DataAccess will go to the database to fetch its value.

    Regards,
    Ady
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  6. Olivier
    Olivier avatar
    146 posts
    Member since:
    Nov 2010

    Posted 10 Mar 2015 in reply to Ady Link to this post

    Hello Ady,

    I need sql statement :
    1- cause my application is build on this fundation
    2- i don't want to rewrite all my web app
    3- I want to add a condition in the where to filter a security response of data., for app need.

    I see on my last query they start the sp procedure, to get only timestamp column. May be they attempt for later concurrency statement  ? i prefer get an error, to write the field which his want, instead of to start a procedure behind me.
    Do you understand ? can you purpose me a good syntax to start sql.

    If it's not possible to manage telerik dataaccess, for several cause :
    1- all the demo don't show for all control binding with telerik access
    2- We are not free with the framework and still lock with the little problem.

    It's better to use Entity Framework

    Look the simply way :

    using (var context = new BloggingContext())
    {
        var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();
    }


    http://www.entityframeworktutorial.net/Querying-with-EDM.aspx

  7. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 12 Mar 2015 Link to this post

    Hello,

    You can use both Telerik Data Access and string-based SQL queries via our ADO API. In this article you can find sample similar to the one you have posted. Please note that if you are not selecting the complete list of table columns you can't have the result in the form of strongly typed persistent type as  you can when using LINQ and projections.

    Regarding the call to SP_PREPEXEC procedure before each SQL query - this is configurable behavior that is introduced to get the best possible performance out of the SQL server by preparing the SQL statement the first time they are executed and then only reusing the already executed statements after that. If you would like to avoid that behavior you can switch it off from the RuntimeConfiguration.PrepareCommands configuration flag.

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner