Database indexes for SQL Server 2005?

[Spartan]

I saw a post somewhere that mentioned indexes that could be applied to a SQLLite installation to improve performance.

Does anyone have a list of what these indexes might be? The download that is provided is an executable, so there is no way to get the exact indexes from them. I'd like to apply them to my SQL Server 2005 / GBPVR installation.

Thanks!

[SniperDude]

This is what I seen created..

create index idxstarttime on PROGRAMME (start_time)
create index idxendtime on PROGRAMME (end_time)

how is that SQLServer2005 working? are you using the free one from MS?? or a commercial version..
I was considering switching to that as well... the MSDE and .NET are tightly intergrated.. could be great things ...

[Spartan]

This is what I seen created..

create index idxstarttime on PROGRAMME (start_time)
create index idxendtime on PROGRAMME (end_time)

how is that SQLServer2005 working? are you using the free one from MS?? or a commercial version..
I was considering switching to that as well... the MSDE and .NET are tightly intergrated.. could be great things ...

Thanks, I will have to apply those.

I am using SQL Server 2005 developer edition, and it has been working great. I am using it in a client / server environment and have had no problems whatsoever.

[spyork]

Hey Spartan,

How did you convert your Access database to SQL Server 2005? Can we use Sub's Database Changer program or is it best to just upsize with Access? I am interested in using SQL Server 2005 Express, but I want to make sure I convert everything correctly. Thanks...

Spyork

[spyork]

Thanks, I will have to apply those.

I am using SQL Server 2005 developer edition, and it has been working great. I am using it in a client / server environment and have had no problems whatsoever.

Hey Spartan, I noticed in another thread that you used the upsize feature to convert you access database to sql server. I have a quick question about the upsize wizard. On the screen where it asks what table attributes to upsize the following options are listed under table relationships: Use DRI or Use triggers. Which one did you select? I noticed that the SQLite db made use of some triggers, so I'm wondering which option I should select. Any help would be appreciated. Thanks...

[Spartan]

Hey Spartan, I noticed in another thread that you used the upsize feature to convert you access database to sql server. I have a quick question about the upsize wizard. On the screen where it asks what table attributes to upsize the following options are listed under table relationships: Use DRI or Use triggers. Which one did you select? I noticed that the SQLite db made use of some triggers, so I'm wondering which option I should select. Any help would be appreciated. Thanks...

I did the following in Access:

Tools -> Database Utilties -> Upsizing wizard
(Create new database) -> next
(Specify server & new database name) -> next
(Select all tables) -> next
(Select Indexes, Validation Rules, Defaults, Table relationships (DRI), Add timestap to fields (let wizard decide) -> next
(No application changes) -> next
finish

Doing a little reading on DRI -- looks like this creates a number of foreign key constraints on the database, but no triggers. I'm not sure if this is the right thing to use or not, but my GBPVR has been working just fine with this setting.

[spyork]

I did the following in Access:

Tools -> Database Utilties -> Upsizing wizard
(Create new database) -> next
(Specify server & new database name) -> next
(Select all tables) -> next
(Select Indexes, Validation Rules, Defaults, Table relationships (DRI), Add timestap to fields (let wizard decide) -> next
(No application changes) -> next
finish

Doing a little reading on DRI -- looks like this creates a number of foreign key constraints on the database, but no triggers. I'm not sure if this is the right thing to use or not, but my GBPVR has been working just fine with this setting.

Would it be possible for you to post your database settings in the config.xml? Everything seemed to upsize properly, but I get an error when trying to manually update the EPG in the config app. Thanks...

[Spartan]

Would it be possible for you to post your database settings in the config.xml? Everything seemed to upsize properly, but I get an error when trying to manually update the EPG in the config app. Thanks...

What type of error are you seeing? Here are my settings:

From the gbpvr server:

Code:

<!-- Database settings -->
  <DatabaseConnectionString>Provider=SQLNCLI;Data Source=(local);User Id=gbpvr;Password=xxxxx;Database=gbpvr;Trusted_Connection=yes;</DatabaseConnectionString>
  <DatabaseProviderFactory>System.Data.OleDb.OleDbFactory</DatabaseProviderFactory>
  <DatabaseIdentityQuery>select @@identity</DatabaseIdentityQuery>

From my client PC:

Code:

<!-- Database settings -->
  <DatabaseConnectionString>Provider=SQLNCLI;Data Source=192.168.2.2;User Id=gbpvr;Password=xxxxx;Database=gbpvr;Trusted_Connection=yes;</DatabaseConnectionString>
  <DatabaseProviderFactory>System.Data.OleDb.OleDbFactory</DatabaseProviderFactory>
  <DatabaseIdentityQuery>select @@identity</DatabaseIdentityQuery>

[spyork]

What type of error are you seeing? Here are my settings:

I'm getting the following error:
Your EPG source does not appear to be configured correctly. Please check the EPG Source Settings and try again.
System.Data.OleDb.OleDbException: The name 'Y' is not permitted in this contect. Only constants, expressions, or variables allowed here. Column names are not permitted.

I have tried both SQL Server 2005 Express and SQL Server 2000 Developer and have the same problem. The only thing really different about my connection string was the Provider=sqloledb instead of Provider=SQLNCLI. There is a thread about this problem already in existence, but since you seemed to be working fine I figure there has to be some sort of explanation. Thanks...

[Spartan]

I'm getting the following error:
Your EPG source does not appear to be configured correctly. Please check the EPG Source Settings and try again.
System.Data.OleDb.OleDbException: The name 'Y' is not permitted in this contect. Only constants, expressions, or variables allowed here. Column names are not permitted.

I have tried both SQL Server 2005 Express and SQL Server 2000 Developer and have the same problem. The only thing really different about my connection string was the Provider=sqloledb instead of Provider=SQLNCLI. There is a thread about this problem already in existence, but since you seemed to be working fine I figure there has to be some sort of explanation. Thanks...

Perhaps it is because I am using the IxmlTV plugin and not using a default gbpvr EPG loader?

http://forums.nextpvr.com/showthread.php?t=16209