Sql Server table stored in a searchable java table?

[ck9663]

I have a table on sql server (~40K rows). I need the content of this table be stored in some sort of table in java that I can search on so that I don't need to keep on running a select on the sql-server.

Running a query to do a select on this table is fast, but am running a 15M rows text file against it, so it's affecting the performance.

Thanks guys.

-- CK

[BigDaddyLH]

Check out JavaDB: http://developers.sun.com/javadb/index.jsp

It is a 100% Java database that can be run as a separate server process or embedded in your application. I'm thinking you could copy data from your external database into an embedded database and then take it from there.

[ck9663]

Thanks. I'll check this out.

-- CK

[ck9663]

There's a problem, Derby can only be access by one application at a time. The java script that I is being controlled by a scheduler. It will monitor a folder if a new file is dropped on that folder and will start a job. Since the execution time of each job varies depending on the file size, there's a probability that two job could run simultaneously.

I'm thinking of just creating a simple table in java and populate it with data form sql server table. Will array be efficient enough for this? Table size = 40K rows.

Thanks

-- CK

[BigDaddyLH]

There's a problem, Derby can only be access by one application at a time. The java script that I is being controlled by a scheduler. It will monitor a folder if a new file is dropped on that folder and will start a job. Since the execution time of each job varies depending on the file size, there's a probability that two job could run simultaneously.

I'm thinking of just creating a simple table in java and populate it with data form sql server table. Will array be efficient enough for this? Table size = 40K rows.

First, by "Java script" you mean "Java program", not "Javascript ", right?

As for the probability of running two instances of the application at the same time, if you want to avoid that, there is a standard way to detect if a program is already running: have the program first acquire a server socket at a determined port. If it can't acquire this server socket, it's a sign that the program is already running.

Finally, an array or java.util.List of length 40K is not large.

[JosAH]

There's a problem, Derby can only be access by one application at a time.

That's only true for the 'embedded driver'; the other one (I can't find its name
right now in my mess) allows multiple connections from multiple applications
to its database; but the reading of the text file is probably much much slower
than the data retrieval from the database.

kind regards,

Jos

[ck9663]

Yes, I mean Java, sorry.

Yes, I missed that part on the documentation. It says server framework allows multiple connections. I think that's what I need. I'll try array first.

The java code that I am running grabs the lookup table on the first part of the code. Then it will read every record on the file (15M records). From this record, I need to check 20-30 fields if it's existing on the look table. However, the third-party app I'm using does not allow me to create a single function to do this. I end up creating 30 function to search the 30 fields (one for each). I know, tell me about it.That's a limitation that they're still working on. Until they fix it, am stuck with this.

So the function will read this array 30 times x 15M rows.

Between the options of reading an array 30 times per row and connecting to Derby 30 times per row would you suggest using Derby or array?

Array size would be 40K rows x 10 columns.

Thanks

-- CK

[BigDaddyLH]

Rather than guessing I'd whip up some code and time it.