Encerrado

SQL Optimization/Reorganization of tables

I think the best way to explain the problem is to do a little create/insert/pseudo code. If you understand what I'm doing here you'll easily see the problem, and I'm ready to hire you.

--BUILD--

Create table table1(info1 varchar(20),info2 varchar(20),badcolumn varchar(390));

Insert into table1 values('data1','data2','ABCDEFGHIJKLMNO...[repeat for 390 chars]');

^^ Repeat for 100,000,000 rows.

--QUERY SCRIPT--

Select badcolumn from table1 where info1 = 'data1' and info2 = 'data2';

> 100,000 Rows Returned

// Split badcolumn into chunks of three characters. EG: "ABC","DEF","GHI",etc.

Select count(*) from table1 where badcolumn like '%ABC%';

Select count(*) from table1 where badcolumn like '%DEF%';

Select count(*) from table1 where badcolumn like '%GHI%';

and so on.

RESULT:

"ABC" was found 1,434 times.

"DEF" was found 4,245 times.

"GHI" was found 42,909 times.

etc.

The obvious massive problem is the count using a wildcard - that's slow. The other problem I have is that the SQL server is remote and on a VERY slow connection so the 100,000 rows returned in the first query can take a long time.

Because there are 100,000,000 rows of badcolumn at 390 characters each, I can't reasonably split it into read-made chunks of three in the database. It would end up somewhere in the 10 billion+ rows area and many many gb. But maybe that's a viable solution?

I need someone that's capable of reworking this entire system to get the final result as efficiently as possible. I can upgrade the hardware but the network connection between the web server and the sql server is stuck on slow.

This is the sum of the problem so please suggest your solution along with your bid!

Habilidades: SQL

Ver mais: reorganization tables sql, the hire connection, i need to hire sql, hire select, t-sql with, t-sql in, best remote, ABC, web optimization, t-sql;, sql, Sql code, Query optimization, pseudo code, m@sum, hire sql, ghi, database optimization, build sql, build database sql, table optimization, rows insert, sql final, web server optimization, sql maybe

Acerca do Empregador:
( 2 comentários ) Jasper, United States

ID do Projeto: #562342

9 freelancers estão ofertando em média $174 para este trabalho

AlosDeveloper

Hello, please see PMB

$170 USD em 1 dia
(28 Comentários)
6.1
soner

I can offer my high quality programming services to you. Please contact if interested.

$220 USD in 0 dias
(4 Comentários)
4.6
GlobalTechInc

Greetings! We would love to work on optimizing your SQL Query. Please check PMB for the details of what we can do. Thank you. Best Regards, RJ Global Tech Development, Inc.

$150 USD in 2 dias
(2 Comentários)
4.0
tfcarlos

I am expert in Sql Server. Microsoft certified Professsional MCPID 5489671. See PMB.

$200 USD in 3 dias
(1 Comentário)
4.0
peruvianmaster

see your pmb please

$150 USD in 2 dias
(7 Comentários)
2.8
frontleague

Hi, please see PM for detailed information. Thanks

$200 USD in 3 dias
(0 Comentários)
0.0
afink

easy doable using proper database design.

$200 USD em 1 dia
(0 Comentários)
0.0
RedSpyder

Hi, I am IT professional with over 20 years experience of SQL. The optimum solution here would appear to be a stored procedure to prepare the report. Obviously with that much data the temporary tables could get quite b Mais

$125 USD in 2 dias
(0 Comentários)
0.0
metwed

it is easy to reduce the three SQL statements with the following query: Select (case when badcolumn like '%ABC%' then 1 else 0 end) C1, (case when badcolumn like '%DEF%' then 1 else 0 end) C2, (case when badcolum Mais

$150 USD in 2 dias
(0 Comentários)
0.0