MySqlDataAdapter.fill of a datatable very slow!

V

vejita866

Member
Joined
Feb 25, 2012
Messages
15
Programming Experience
Beginner
Hello guys,
i have an issue with the code above.
When i make the DataAdapter.Fill(table) of a very little select of about 30 row with about 10 colums, it gets very slow.
any suggestion?
Dim table As New DataTable()
Try


Dim mycmd As New MySqlCommand(querymysql, conn)
Dim DataAdapter As New MySqlDataAdapter(mycmd)
Dim commandBuilder As New MySqlCommandBuilder(DataAdapter)



DataAdapter.Fill(table)
Catch sqlExc As MySqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _ MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
datagrid1.datasource = table
Click to expand...
 
Sort by date Sort by votes
here the dump of the tables..

USE [bookseller]
GO
/****** Object: Table [dbo].[Abbonamenti] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Abbonamenti](
[abbonamento] [int] NOT NULL,
[idcliente] [int] NOT NULL,
[data_creazione] [int] NULL,
[data_cancellazione] [int] NULL,
CONSTRAINT [PK_Abbonamenti] PRIMARY KEY CLUSTERED
(
[abbonamento] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Abbonamenti_Fumetti] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Abbonamenti_Fumetti](
[id] [int] NOT NULL,
[idtestata] [int] NOT NULL,
[idabbonamento] [int] NOT NULL,
[data_inserimento] [int] NOT NULL,
CONSTRAINT [PK_Abbonamenti_Fumetti] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Autori] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Autori](
[id] [int] IDENTITY(999,1) NOT NULL,
[nome] [varchar](max) NULL,
[idnazionalità] [int] NULL,
[descrizione] [varchar](max) NULL,
CONSTRAINT [PK_Autori] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Clienti] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Clienti](
[id] [int] NOT NULL,
[nome] [varchar](255) NOT NULL,
[cognome] [varchar](255) NOT NULL,
[indirizzo] [varchar](255) NOT NULL,
[città] [varchar](255) NOT NULL,
[cap] [int] NOT NULL,
[provincia] [varchar](2) NOT NULL,
[telefono] [varchar](255) NOT NULL,
[cellulare] [varchar](255) NOT NULL,
[fax] [varchar](255) NOT NULL,
[note] [varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[data_creazione] [varchar](20) NOT NULL,
[data_nascita] [varchar](20) NOT NULL,
CONSTRAINT [PK_Clienti] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Collane] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Collane](
[id] [int] IDENTITY(1,1) NOT NULL,
[nome] [varchar](255) NOT NULL,
[descrizione] [text] NULL,
CONSTRAINT [PK_Collane] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Editori] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Editori](
[id] [int] NOT NULL,
[nome] [varchar](255) NOT NULL,
[CMETSCO] [varchar](222) NOT NULL,
[CSCONOT] [varchar](999) NOT NULL,
[CINDIR] [varchar](222) NOT NULL,
[mail] [varchar](999) NOT NULL,
[sito] [varchar](999) NOT NULL,
[CISBN] [varchar](255) NOT NULL,
[CISSN] [varchar](255) NOT NULL,
[CSCOFIS] [varchar](255) NOT NULL,
[CSCOVAR] [tinyint] NOT NULL,
CONSTRAINT [PK_Editori] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Fumettiautori] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Fumettiautori](
[id] [int] IDENTITY(1,1) NOT NULL,
[idvolume] [int] NOT NULL,
[idautore] [int] NOT NULL,
[idruolo] [int] NULL,
CONSTRAINT [PK_Fumettiautori] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Reparto] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Reparto](
[id] [int] NOT NULL,
[nome_reparto] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Ruoli] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Ruoli](
[id] [int] IDENTITY(1,1) NOT NULL,
[descrizione] [varchar](255) NOT NULL,
CONSTRAINT [PK__Ruoli__3213E83FEF7CC4D7] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Testate] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Testate](
[id] [int] IDENTITY(800,1) NOT NULL,
[titolo] [varchar](255) NOT NULL,
[sottotitolo] [varchar](255) NULL,
[idcollana] [int] NULL,
[ideditore] [int] NULL,
[idreparto] [int] NULL,
[data_inserimento] [int] NULL,
[note] [varchar](255) NULL,
CONSTRAINT [PK__Testate__3213E83FF73A5B03] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Volumi] Script Date: 05/08/2012 11:28:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Volumi](
[id] [int] IDENTITY(1,1) NOT NULL,
[idtestata] [int] NOT NULL,
[barcode] [varchar](255) NOT NULL,
[titolo] [varchar](255) NOT NULL,
[sottotitolo] [varchar](255) NOT NULL,
[numero] [int] NOT NULL,
[prezzo] [numeric](10, 2) NOT NULL,
[data_pubblicazione] [int] NOT NULL,
[esaurito] [tinyint] NOT NULL,
[quantita] [int] NOT NULL,
[quantita_magazzino] [int] NOT NULL,
[scorta_minima] [int] NOT NULL,
[note] [text] NOT NULL,
[data_ultimamodifica] [int] NOT NULL,
CONSTRAINT [PK__Volumi__3213E83F85191F00] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Abbonamenti_Fumetti] WITH CHECK ADD CONSTRAINT [FK_Abbonamenti_Fumetti_Abbonamenti] FOREIGN KEY([idabbonamento])
REFERENCES [dbo].[Abbonamenti] ([abbonamento])
GO
ALTER TABLE [dbo].[Abbonamenti_Fumetti] CHECK CONSTRAINT [FK_Abbonamenti_Fumetti_Abbonamenti]
GO
ALTER TABLE [dbo].[Abbonamenti_Fumetti] WITH CHECK ADD CONSTRAINT [FK_Abbonamenti_Fumetti_Testate] FOREIGN KEY([idtestata])
REFERENCES [dbo].[Testate] ([id])
GO
ALTER TABLE [dbo].[Abbonamenti_Fumetti] CHECK CONSTRAINT [FK_Abbonamenti_Fumetti_Testate]
GO
ALTER TABLE [dbo].[Fumettiautori] WITH CHECK ADD CONSTRAINT [FK_Fumettiautori_Autori] FOREIGN KEY([idautore])
REFERENCES [dbo].[Autori] ([id])
GO
ALTER TABLE [dbo].[Fumettiautori] CHECK CONSTRAINT [FK_Fumettiautori_Autori]
GO
ALTER TABLE [dbo].[Fumettiautori] WITH CHECK ADD CONSTRAINT [FK_Fumettiautori_Ruoli] FOREIGN KEY([idruolo])
REFERENCES [dbo].[Ruoli] ([id])
GO
ALTER TABLE [dbo].[Fumettiautori] CHECK CONSTRAINT [FK_Fumettiautori_Ruoli]
GO
ALTER TABLE [dbo].[Fumettiautori] WITH CHECK ADD CONSTRAINT [FK_Fumettiautori_Volumi] FOREIGN KEY([idvolume])
REFERENCES [dbo].[Volumi] ([id])
GO
ALTER TABLE [dbo].[Fumettiautori] CHECK CONSTRAINT [FK_Fumettiautori_Volumi]
GO
ALTER TABLE [dbo].[Testate] WITH CHECK ADD CONSTRAINT [FK_Testate_Collane] FOREIGN KEY([idcollana])
REFERENCES [dbo].[Collane] ([id])
GO
ALTER TABLE [dbo].[Testate] CHECK CONSTRAINT [FK_Testate_Collane]
GO
ALTER TABLE [dbo].[Testate] WITH CHECK ADD CONSTRAINT [FK_Testate_Editori] FOREIGN KEY([ideditore])
REFERENCES [dbo].[Editori] ([id])
GO
ALTER TABLE [dbo].[Testate] CHECK CONSTRAINT [FK_Testate_Editori]
GO
ALTER TABLE [dbo].[Testate] WITH CHECK ADD CONSTRAINT [FK_Testate_Reparto] FOREIGN KEY([idreparto])
REFERENCES [dbo].[Reparto] ([id])
GO
ALTER TABLE [dbo].[Testate] CHECK CONSTRAINT [FK_Testate_Reparto]
GO


[/QUOTE]
Click to expand...
 
Upvote 0 Downvote

Similar threads

D
  • Question
Resolved create a class that read data from db with Repeater Control?
Replies
2
Views
1K
DARK__FOXX
D
V
  • Question
Question Get SQL Server Instances and Databases For A Selected Instance
Replies
1
Views
3K
jmcilhinney
jmcilhinney
R
  • Question
Question How to start exporting data to an excel file from a particular row of excel sheet?
Replies
4
Views
2K
jmcilhinney
jmcilhinney
W
  • Question
Question Refresh a combobox in a parent tabpage after making an entry in child tabpage
Replies
9
Views
11K
jmcilhinney
jmcilhinney
dgulliver
  • Question
Unable to connect to any of the specified MySQL hosts
Replies
1
Views
6K
marktoddy
M

Share this page

Latest posts

Back
Top