Universe Entity Framework very slow to select

Question

I am going with the Entity Framework code first approach and am finding that queries to grab just 350 records or so is taking about 8 seconds. How can I speed this up? Is this Universe or Entity Framework that is being slow?

Entity Framework 5.0 U2.Data.Client 1.2.1 .Net Framework 4.5.1

RAMContext looks something like this :

public class RAMContext : DbContext
{
    public RAMContext() { }

    public DbSet<Policy> Policies { get; set; }
}

Here is the code to grab the entities :

List<Policy> policies = null;

Database.SetInitializer<RAMContext>(null);
using (RAMContext context = new RAMContext())
{
    policies = (from p in context.Policies
               where p.AGENT_NO == id
               select p).ToList();
}

Here is the connection string :

<add name="RAMContext" connectionString="Database=<account>;UserID=<userid>;Password=<pwd>;Server=<server>;Pooling=false;ServerType=universe;ConnectTimeout=360;SleepAfterClose=300;PersistSecurityInfo=true" providerName="U2.Data.Client" />

AGENT_NO is indexed and the same query ran directly on the DB from TCL finishes almost instantly.

EDITED After the comments from Rajan I tried the following :

policies = (from p in context.Policies
            where p.AGENT_NO == id
            select new PolicyModel
            {
                //Type = PolicyModel.Types)StringValue.GetEnumValueByStringValue(typeof(PolicyModel.Types), p.TYPE),
                Insured = p.INSURED,
                City = p.CITY,
                State = p.STATE,
                CancelDate = p.CANC_DT
                //IsNew = PickHelper.PickYNNullToBool(p.NEW_RENEW_FLG)
            });

I am able to make this select in under 3 seconds now, it appears. I also rebuilt the index on AGENT_NO and I believe that helped considerably.

Trying your second suggestion I get the following exception :

An exception of type 'System.IndexOutOfRangeException' occurred in U2.Data.Client.dll but was not handled in user code

Additional information:

Invalid index -1 for this U2ParameterCollection with Count=0.

Answer 1

Could you please try the following code? This code does not use Entity Framework (LINQ to Entity). We want to see how UniVerse is behaving without Entity Framework.

You need to do the following:

• Create C# Console application
• Replace Program.cs file’ s content with the following code
• Right Click->Add Reference -> C:\Program Files (x86)\Rocket Software\U2 Toolkit for .NET\U2 Database Provider\bin.NETFramework\v4.0\U2.Data.Client.dll
• Change connection string
• Change SQL syntax
• Run the program. See below screen shot. It shows “Time Taken in Seconds”.

Code

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using U2.Data.Client;
    using System.Data;
    using System.Diagnostics;

    namespace DataAdapter
    {
        class Program
        {
            static void Main(string[] args)
            {
                try
                {
                    Console.WriteLine("start.........................");
                    Stopwatch sw = new Stopwatch();
                    sw.Start();

                    U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
                    conn_str.UserID = "administrator";
                    conn_str.Password = "pass";
                    conn_str.Server = "localhost";
                    conn_str.Database = "XDEMO";
                    conn_str.ServerType = "UNIVERSE";
                    conn_str.Pooling = false;
                    string s = conn_str.ToString();
                    U2Connection con = new U2Connection();
                    con.ConnectionString = s;

                    con.Open();
                    Console.WriteLine("Connected...");
                    U2Command cmd = con.CreateCommand();
                    cmd.CommandText = "SELECT * FROM PRODUCTS";
                    DataSet ds = new DataSet();
                    U2DataAdapter da = new U2DataAdapter(cmd);
                    da.Fill(ds);

                    sw.Stop();

                    TimeSpan elapsed = sw.Elapsed;
                    string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", elapsed.Hours, elapsed.Minutes, elapsed.Seconds, elapsed.Milliseconds / 10);
                    int nSec = elapsed.Seconds;
                    con.Close();
                    Console.WriteLine("Time Taken in seconds:" + elapsedTime);
                    Console.WriteLine("End........................... ");

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);

                }
                finally
                {
                    Console.WriteLine("Enter to exit:");
                    string line = Console.ReadLine();
                }
            }
        }
    }